示例:常用操作

  1. import psycopg2
  2. # psycopg2常用链接方式
  3. 1. conn = psycopg2.connect(dbname="postgres", user="user", password="password", host="localhost", port=port)
  4. 2. conn = psycopg2.connect("dbname=postgres user=user password=password host=localhost port=port")
  5. # 创建连接对象
  6. conn=psycopg2.connect(database="postgres",user="user",password="password",host="localhost",port=port)
  7. cur=conn.cursor() #创建指针对象
  8. # 创建连接对象(SSl连接)
  9. conn = psycopg2.connect(dbname="postgres", user="user", password="password", host="localhost", port=port,
  10. sslmode="verify-ca", sslcert="client.crt",sslkey="client.key",sslrootcert="cacert.pem")
  11. 注意: 如果sslcert, sslkey,sslrootcert没有填写,默认取当前用户.postgresql目录下对应的client.crt
  12. client.key root.crt
  13. # 创建表
  14. cur.execute("CREATE TABLE student(id integer,name varchar,sex varchar);")
  15. # 插入数据
  16. cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(1,'Aspirin','M'))
  17. cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(2,'Taxol','F'))
  18. cur.execute("INSERT INTO student(id,name,sex) VALUES(%s,%s,%s)",(3,'Dixheral','M'))
  19. # 获取结果
  20. cur.execute('SELECT * FROM student')
  21. results=cur.fetchall()
  22. print (results)
  23. # 关闭连接
  24. conn.commit()
  25. cur.close()
  26. conn.close()
  27. # 使用日志
  28. import logging
  29. import psycopg2
  30. from psycopg2.extras import LoggingConnection
  31. logging.basicConfig(level=logging.DEBUG) # 日志级别
  32. logger = logging.getLogger(__name__)
  33. db_settings = {
  34. "user": "user",
  35. "password": "password",
  36. "host": "localhost",
  37. "database": "postgres",
  38. "port": port
  39. }
  40. conn = psycopg2.connect(connection_factory=LoggingConnection, **db_settings)
  41. conn.initialize(logger)