Python & PostgreSQL

本篇介绍用Python操作PostgreSQL数据，本文的参考教程是 PostgreSQL Python

根据官方的解释 Python - PostgreSQL wiki，建议使用psycopg2

1. 首先，你要有一个数据库，所以使用以下SQL语句，创建一个名为suppliers的数据库，本文中的操作都是围绕这一数据库展开的。

CREATE DATAbase suppliers;

2. 在Python中连接数据库：

（1）最直接的办法，使用psycopg2中的connect函数

conn = psycopg2.connect

（2）另一种方法，把连接数据库时用到的参数都列出来 （效果同上）

conn = psycopg2.connect

这里涉及到的参数有：

• database: the name of the database that you want to connect.
• user: the username used to authenticate.
• password: password used to authenticate.
• host: database server address e.g., localhost or an IP address
• port: the port number that defaults to 5432 if it is not provided.

（3）注意：还可以使用配置文件（configuration file ），这种好处是把所有的参数放在了一个文件里，查找和修改就比较方便了。

database.ini

[postgresql]host=localhostdatabase=suppliersuser=postgrespassword=postgres

3. 放在一起：connect代码块

首先写 config 函数，这一函数会读取配置文件 database.ini 来获取文件中用于数据库连接的参数. 我们可以保存以下代码块在config.py文件中，以便之后的重复调用。

#!/usr/bin/pythonfrom configparser import ConfigParserdef config: # create a parser parser = ConfigParser # read config file parser.read # get section, default to postgresql db = {} if parser.has_section: params = parser.items for param in params: db[param[0]] = param[1] else: raise Exception) return db

接下来写 connect 函数。这一函数用于连接数据库，在以下的代码块中，connect 函数帮助我们连接到 suppliers 数据库，并且打印出PostgreSQL数据库的版本号.

#!/usr/bin/pythonimport psycopg2from config import config # 这是上面的config代码块，已经保存在config.py文件中def connect: """ Connect to the PostgreSQL database server """ conn = None try: # read connection parameters params = config # connect to the PostgreSQL server print conn = psycopg2.connect # create a cursor cur = conn.cursor # execute a statement print cur.execute') # display the PostgreSQL database server version db_version = cur.fetchone print # close the communication with the PostgreSQL cur.close except as error: print finally: if conn is not None: conn.close printif __name__ == '__main__': connect

4. 总结一下

工作流程如下，这实际上也是Python对数据库操作的基本流程。

• 从 database.ini 文件中读取连接数据库的参数
• 通过调用 connect 函数连接数据库
• 建立新的 cursor，执行（execute) SQL 语句
• 对于cursor对象，调用fetchone 来读取查询的结果
• 操作完成后，要记得关闭和数据库的连接，使用close。cursor 和connection 都需要关闭哦

1. 数据库是一个库，数据是存储在表（table）里的，所以在操作数据前，我们需要在数据库中新建表格

SQL语句

CREATE TABLE

Python代码块

#!/usr/bin/pythonimport psycopg2from config import configdef create_tables: """ create tables in the PostgreSQL database""" commands = NOT NULL ) """, """ CREATE TABLE parts NOT NULL ) """, """ CREATE TABLE part_drawings NOT NULL, drawing_data BYTEA NOT NULL, FOREIGN KEY REFERENCES parts ON UPDATE CASCADE ON DELETE CASCADE ) """, """ CREATE TABLE vendor_parts , FOREIGN KEY REFERENCES vendors ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY REFERENCES parts ON UPDATE CASCADE ON DELETE CASCADE ) """) conn = None try: # read the connection parameters params = config # connect to the PostgreSQL server conn = psycopg2.connect cur = conn.cursor # create table one by one for command in commands: cur.execute # close communication with the PostgreSQL database server cur.close # commit the changes conn.commit except as error: print finally: if conn is not None: conn.closeif __name__ == '__main__': create_tables

在数据库中使用 dt 可以列出数据库中的表

suppliers=# dt List of relations Schema | Name | Type | Owner--------+---------------+-------+---------- public | part_drawings | table | postgres public | parts | table | postgres public | vendor_parts | table | postgres public | vendors| table | postgres

2. 将数据导入表格

（1）使用insert的方式将数据导入表格

步骤如下：

a. connect

conn = psycopg2.connect

b. cursor

cur = conn.cursor

c. execute

cur.execute)

d. fetchone

id = cur.fetchone[0]

c. commit

conn.commit

d. close

cur.closeconn.close

（2）将一行数据插入表中

Inserting one row into a PostgreSQL table example

#!/usr/bin/pythonimport psycopg2from config import configdef insert_vendor: """ insert a new vendor into the vendors table """ sql = """INSERT INTO vendors VALUES RETURNING vendor_id;""" conn = None vendor_id = None try: # read database configuration params = config # connect to the PostgreSQL database conn = psycopg2.connect # create a new cursor cur = conn.cursor # execute the INSERT statement cur.execute) # get the generated id back vendor_id = cur.fetchone[0] # commit the changes to the database conn.commit # close communication with the database cur.close except as error: print finally: if conn is not None: conn.close return vendor_id

（3）将多行数据插入表中

Inserting multiple rows into a PostgreSQL table example

def insert_vendor_list: """ insert multiple vendors into the vendors table """ sql = "INSERT INTO vendors VALUES" conn = None try: # read database configuration params = config # connect to the PostgreSQL database conn = psycopg2.connect # create a new cursor cur = conn.cursor # execute the INSERT statement cur.executemany # commit the changes to the database conn.commit # close communication with the database cur.close except as error: print finally: if conn is not None: conn.close

（4）使用以上代码块

To test the insert_vendor and insert_vendor_list functions, you use the following code snippet:

if __name__ == '__main__': # insert one vendor insert_vendor # insert multiple vendors insert_vendor_list, , , , , ])

3. 更新表格中的数据

#!/usr/bin/pythonimport psycopg2from config import configdef update_vendor: """ update vendor name based on the vendor id """ sql = """ UPDATE vendors SET vendor_name = %s WHERE vendor_id = %s""" conn = None updated_rows = 0 try: # read database configuration params = config # connect to the PostgreSQL database conn = psycopg2.connect # create a new cursor cur = conn.cursor # execute the UPDATE statement cur.execute) # get the number of updated rows updated_rows = cur.rowcount # Commit the changes to the database conn.commit # Close communication with the PostgreSQL database cur.close except as error: print finally: if conn is not None: conn.close return updated_rows

1. fetchone

def get_vendors: """ query data from the vendors table """ conn = None try: params = config conn = psycopg2.connect cur = conn.cursor cur.execute print row = cur.fetchone while row is not None: print row = cur.fetchone cur.close except as error: print finally: if conn is not None: conn.close

调用：

if __name__ == '__main__': get_vendors

结果：

The number of parts: 7

2. fetchall

def get_parts: """ query parts from the parts table """ conn = None try: params = config conn = psycopg2.connect cur = conn.cursor cur.execute rows = cur.fetchall print for row in rows: print cur.close except as error: print finally: if conn is not None: conn.close

调用：

if __name__ == '__main__': get_parts

结果：

The number of parts: 6

3. fetchmany

def iter_row: while True: rows = cursor.fetchmany if not rows: break for row in rows: yield rowdef get_part_vendors: """ query part and vendor data from multiple tables""" conn = None try: params = config conn = psycopg2.connect cur = conn.cursor cur.execute for row in iter_row: print cur.close except as error: print finally: if conn is not None: conn.close

调用：

if __name__ == '__main__': get_part_vendors

结果：