使用 psycopg2 连接 PostgreSQL

来自 PostgreSQL 维基
跳转到导航跳转到搜索

Psycopg2 是一个成熟的驱动程序，用于从 Python 脚本语言与 PostgreSQL 交互。它用 C 语言编写，并提供了一种执行针对 PostgreSQL 数据库的全部范围的 SQL 操作的方式。本页仅关注驱动程序的版本 2。

概述

链接

功能

  • 多个连接/连接对象（不强制使用单例）
  • 事务管理/方法
  • 将列作为带有列名的 Python 字典（哈希）返回
  • 自动过滤
  • 游标对象
  • 连接池（将在稍后添加示例）
  • 异步查询（线程安全）

历史

此页面最初由 Bendermott 开发，并由 Jonjensen 等人贡献。烹饪书风格仍然存在，但是页面正在开发中以现代化以适应 Python 3。要查看 Python 2 特定的示例，请使用来自 2015 年的 最后一个版本。


示例

在以下示例中，我不会假设您完全熟悉 Python；因此，我提供了过多的注释，并且每个示例都是一个完整的脚本，而不仅仅是一段代码片段。

您可能想将示例复制到您最喜欢的代码编辑器中以进行语法突出显示！

所有代码示例都在运行 OpenSUSE Linux 的 Python 2.6 上进行了测试。

连接到 Postgres

使用身份验证连接到 Postgres 数据库。如果发生错误，请捕获并打印连接错误。

#!/usr/bin/python
import psycopg2
import sys

def main():
	#Define our connection string
	conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"

	# print the connection string we will use to connect
	print "Connecting to database\n	->%s" % (conn_string)

	# get a connection, if a connect cannot be made an exception will be raised here
	conn = psycopg2.connect(conn_string)

	# conn.cursor will return a cursor object, you can use this cursor to perform queries
	cursor = conn.cursor()
	print "Connected!\n"

if __name__ == "__main__":
	main()

执行选择

此示例展示了如何连接到数据库，然后获取和使用游标对象从表中检索记录。

在此示例中，我们将假设您的数据库在公共模式中名为“my_database”，并且您要从中选择的表名为“my_table”。

#!/usr/bin/python
import psycopg2
import sys
import pprint

def main():
	conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
	# print the connection string we will use to connect
	print "Connecting to database\n	->%s" % (conn_string)

	# get a connection, if a connect cannot be made an exception will be raised here
	conn = psycopg2.connect(conn_string)

	# conn.cursor will return a cursor object, you can use this cursor to perform queries
	cursor = conn.cursor()

	# execute our Query
	cursor.execute("SELECT * FROM my_table")

	# retrieve the records from the database
	records = cursor.fetchall()

	# print out the records using pretty print
	# note that the NAMES of the columns are not shown, instead just indexes.
	# for most people this isn't very useful so we'll show you how to return
	# columns as a dictionary (hash) in the next example.
	pprint.pprint(records)

if __name__ == "__main__":
	main()

使用列名选择/提取记录

在此示例中，我们将执行与上面相同的 select 操作，但这次我们将返回列作为 Python 字典，以便列名存在。

我们还将使用 Psycopg2 的 prinf 风格的变量替换，以及不同的提取方法来返回一行（fetchone）。

#!/usr/bin/python
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys

def main():
	conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
	# print the connection string we will use to connect
	print "Connecting to database\n	->%s" % (conn_string)

	# get a connection, if a connect cannot be made an exception will be raised here
	conn = psycopg2.connect(conn_string)

	# conn.cursor will return a cursor object, you can use this query to perform queries
	# note that in this example we pass a cursor_factory argument that will
	# dictionary cursor so COLUMNS will be returned as a dictionary so we
	# can access columns by their name instead of index.
	cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

	# tell postgres to use more work memory
	work_mem = 2048

	# by passing a tuple as the 2nd argument to the execution function our
	# %s string variable will get replaced with the order of variables in
	# the list. In this case there is only 1 variable.
	# Note that in python you specify a tuple with one item in it by placing
	# a comma after the first variable and surrounding it in parentheses.
	cursor.execute('SET work_mem TO %s', (work_mem,))

	# Then we get the work memory we just set -> we know we only want the
	# first ROW so we call fetchone.
	# then we use bracket access to get the FIRST value.
	# Note that even though we've returned the columns by name we can still
	# access columns by numeric index as well - which is really nice.
	cursor.execute('SHOW work_mem')

	# Call fetchone - which will fetch the first row returned from the
	# database.
	memory = cursor.fetchone()

	# access the column by numeric index:
	# even though we enabled columns by name I'm showing you this to
	# show that you can still access columns by index and iterate over them.
	print "Value: ", memory[0]

	# print the entire row 
	print "Row:	", memory

if __name__ == "__main__":
	main()

使用服务器端游标提取记录

如果您要从数据库中检索的记录集非常大，或者您希望在不先检索整个表的情况下迭代表中的记录，那么游标正是您需要的。

游标保持数据库连接打开，并按您的要求逐条提取数据库记录。

在 Psycopg2 中有几种方法可以做到这一点，我将向您展示最基本的示例。

有关高级使用情况，请参阅关于 cursor.scroll() 使用的文档，请参阅

#!/usr/bin/python
import psycopg2
#note that we have to import the Psycopg2 extras library!
import psycopg2.extras
import sys

def main():
	conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
	# print the connection string we will use to connect
	print "Connecting to database\n	->%s" % (conn_string)

	conn = psycopg2.connect(conn_string)

	# HERE IS THE IMPORTANT PART, by specifying a name for the cursor
	# psycopg2 creates a server-side cursor, which prevents all of the
	# records from being downloaded at once from the server.
	cursor = conn.cursor('cursor_unique_name', cursor_factory=psycopg2.extras.DictCursor)
	cursor.execute('SELECT * FROM my_table LIMIT 1000')

	# Because cursor objects are iterable we can just call 'for - in' on
	# the cursor object and the cursor will automatically advance itself
	# each iteration.
	# This loop should run 1000 times, assuming there are at least 1000
	# records in 'my_table'
	row_count = 0
	for row in cursor:
		row_count += 1
		print "row: %s    %s\n" % (row_count, row)

if __name__ == "__main__":
	main()
取自 "https://wiki.postgresql.ac.cn/index.php?title=Using_psycopg2_with_PostgreSQL&oldid=36791"