Python - 使用 PostgreSQL 数据库

基本用法

# -*- coding: utf-8 -*-

# !/usr/bin/python

# 需要安装下面的驱动包
import psycopg2

# 连接到一个现有的数据库，如果数据库不存在，那么它就会被创建，最终将返回一个数据库对象。
conn = psycopg2.connect(database="aoye", user="Chris", password="123456", host="127.0.0.1", port="5432")
print("Opened database successfully")

# 创建表：
cur = conn.cursor()
# cur.execute('''CREATE TABLE COMPANY
#        (ID INT PRIMARY KEY     NOT NULL,
#        NAME           TEXT    NOT NULL,
#        AGE            INT     NOT NULL,
#        ADDRESS        CHAR(50),
#        SALARY         REAL);''')
# print ("Table created successfully")

# 插入数据
# cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (1, 'Paul', 32, 'California', 20000.00 )")
#
# cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
#
# cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
#
# cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
#       VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
# print("Records created successfully")

# 查询数据
cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print("ID = ", row[0])
   print("NAME = ", row[1])
   print("ADDRESS = ", row[2])
   print("SALARY = ", row[3], "\n")

print("Operation done successfully")

# 更新数据
# cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")

# 删除数据
# cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
conn.close()

插入数据

• 这里需要留意，插入的数据需要用如下方式传入（有些数据内容包含单引号，必须使用下面的方式插入）

while True:
    url = f.readline().split()[1]
    if not url:
        break
    else:
        sql = """INSERT INTO urls(md5,url) VALUES (%s, %s)"""
        cur.execute(sql,(get_md5(url), url))
        conn.commit()

参考文档：
https://www.cnblogs.com/Erick-L/p/7106816.html