python连接postgresql

admin

1.客户端安装psycopg2

1. C:\Users\zhyu>pip install psycopg2
   
2. Collecting psycopg2
   
3.   Using cached psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
   
4. Installing collected packages: psycopg2
   
5. Successfully installed psycopg2-2.8.5

复制代码

2.服务端启动postgresql

1. [postgres@vm1 ~]$ pg_ctl start
   
2. waiting for server to start....2020-05-31 07:40:41.254 CST [2025] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
   
3. 2020-05-31 07:40:41.254 CST [2025] LOG:  listening on IPv4 address "0.0.0.0", port 5432
   
4. 2020-05-31 07:40:41.254 CST [2025] LOG:  listening on IPv6 address "::", port 5432
   
5. 2020-05-31 07:40:41.332 CST [2025] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
   
6. 2020-05-31 07:40:41.517 CST [2025] LOG:  redirecting log output to logging collector process
   
7. 2020-05-31 07:40:41.517 CST [2025] HINT:  Future log output will appear in directory "/app/postgres/12/data".
   
8. done
   
9. server started

复制代码

3.进入python环境

1. Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:37:02) [MSC v.1924 64 bit (AMD64)] on win32
   
2. Type "help", "copyright", "credits" or "license" for more information.
   
3. >>>
   

复制代码

4.加载psycopg2

1. import psycopg2
   

复制代码

5.建立连接符

1. conn=psycopg2.connect(database="postgres",user="postgres",password="postgres",host="192.168.56.101",port="5432")
   

复制代码

6.建立游标

1. cursor=conn.cursor()
   

复制代码

7.执行sql

1. cursor.execute("select version()")
   

复制代码

8.获取结果

1. >>> results=cursor.fetchall()
   
2. >>> print(results)
   
3. [('PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit',)]

复制代码

9.关闭游标

1. cursor.close()

复制代码

10.关闭连接

1. conn.close()

复制代码

整体流程步骤：

1. Python 3.8.3 (tags/v3.8.3:6f8c832, May 13 2020, 22:37:02) [MSC v.1924 64 bit (AMD64)] on win32
   
2. Type "help", "copyright", "credits" or "license" for more information.
   
3. >>> import psycopg2
   
4. >>> conn=psycopg2.connect(database="postgres",user="postgres",password="postgres",host="192.168.56.101",port="5432")
   
5. >>> cursor=conn.cursor()
   
6. >>> cursor.execute("select version()")
   
7. >>> results=cursor.fetchall()
   
8. >>> print(results)
   
9. [('PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit',)]
   
10. >>> cursor.close()
    
11. >>> conn.close()
    
12. >>>

复制代码