从psycopg2.5开始,connection和cursor都是context manager对象,可以在with ... as ...语句中使用。值得注意的是,离开with语句后,connection对象不会被close,它只是结束提交或者回滚事务。所以可以在多个with语句中使用connection对象。
调用存储过程
使用cursor.callproc('function name', tuple), 函数的第一个参数是存储过程的名字,函数的第二个参数是实参tuple。这个调用和cursor.execute('select * from functionanme(%s)', tuple)相同。
import psycopg2
from config import config
def write_blob(path_to_file):
""" insert a BLOB into a table """
conn = None
try:
# read data from a picture
drawing = open(path_to_file, 'rb').read()
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# execute the INSERT statement
cur.execute("INSERT INTO parts_drawings(drawing_data,name) " +
"VALUES(%s,%s)",
(psycopg2.Binary(drawing), path_to_file))
# commit the changes to the database
conn.commit()
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
write_blob('./1.jpg')
read_blob.py
from config import config
import psycopg2
def read_blob(id, path_to_dir):
""" read BLOB data from a table """
conn = None
try:
# read database configuration
params = config()
# connect to the PostgresQL database
conn = psycopg2.connect(**params)
# create a new cursor object
cur = conn.cursor()
# execute the SELECT statement
cur.execute(""" SELECT *
FROM parts_drawings
WHERE id = %s """,
(id,))
blob = cur.fetchone()
open(path_to_dir + str(blob[0]) + '.jpg', 'wb').write(blob[1])
# close the communication with the PostgresQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
read_blob(1, './img/')
查询数据
查询数据和其它操作类似。
可以使用cursor.fetchone, cursor.fetchall, cursor.fetchmany(size=cursor.arraysize)方法来返回查询结果。fetchone返回一个tuple或者None, fetchall返回一个list of tuple,如果没有结果则返回一个空的tuple。fetchmany返回list of tuple, list的长度由size参数决定,size的默认值是cursor.arraysize, 如果没有结果可以返回,那么返回一个空的list。