连接数据库

基本示例

下面是 Psycogp2 的一个基本使用示例

>>> import psycopg2

# 连接到现有数据库
>>> conn = psycopg2.connect("dbname=test user=omm")

# 打开一个游标用来执行数据库操作
>>> cur = conn.cursor()

# 执行一条命令：这个 SQL 将创建一个新表 test
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# 通过参数传递数据以填充 %s 占位符的变量，并让 Psycopg 进行正确的转换
# (这样可以避免 SQL 注入风险!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

# 查询数据库，以 Python 对象的形式获取数据
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")

# 提交，将对数据库的更改持久化
>>> conn.commit()

# 关闭游标和数据库连接
>>> cur.close()
>>> conn.close()

Psycopg 的主要入口是:

• connect() 函数，用来创建一个新的数据库会话并返回一个新的连接实例。

• connection 类封装了数据库会话。它允许:

  1. 使用 cursor() 方法创建新的游标实例来执行数据库命令和查询;
  2. 使用commit()或rollback()方法终止事务。

• cursor 类主要作用是与数据库进行交互:

  1. 使用 execute() 和 executemany() 等方法向数据库发送命令;
  2. 通过迭代或使用 fetchone()、fetchmany()、fetchall() 等方法从数据库中读取数据。

向查询 SQL 传递参数

Psycopg 使用 Python 变量的类型将其转换为对应的 SQL 类型值：Python 类型决定了用于将对象转换为适合的PostgreSQL的字符串表示的函数。许多标准Python类型已经适配了正确的SQL表示。

在 cursor.execute() 等函数中，通过在SQL语句中使用 %s 占位符，并将一系列值作为函数的第二个参数传递给SQL语句。例如:

>>> cur.execute("""
...     INSERT INTO some_table (an_int, a_date, a_string)
...     VALUES (%s, %s, %s);
...     """,
...     (10, datetime.date(2005, 11, 18), "O'Reilly"))

转换后的SQL命令，如下所示:

INSERT INTO some_table (an_int, a_date, a_string)
VALUES (10, '2005-11-18', 'O''Reilly');

注意：不支持在参数名称中包含字符 %，(，)。

当使用参数时，如果在查询中包含字符 %，您可以使用 %% 字符串:

>>> cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # 错误
>>> cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # 正确

虽然这个机制类似于常规的 Python 字符串格式化操作，但在向查询传递参数时，您应该注意如下一些细微的差异。

• 不要直接而使用 Python 字符串格式化：execute() 方法接受一个元组或值字典作为第二个参数。永远不要使用 % 或 + 将参数合并到查SQL中:

  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # 错误
  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # 正确

• 对于基于位置的变量绑定，第二个参数必须始终是一个序列，即使它包含单个变量(请记住，Python 需要逗号来创建单个元素元组):

  >>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # 错误
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # 错误
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # 正确
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # 正确

• 占位符不能加引号。Psycopg 将在需要的地方添加引号:

  >>> cur.execute("INSERT INTO numbers VALUES ('%s')", (10,)) # 错误
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # 正确

• 变量占位符必须始终为 %s，即使其他占位符(例如整数的 %d 或浮点数的 %f)可能看起来更合适:

  >>> cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # 错误
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # 正确

• 只有要查询的值应该通过这种方式绑定：它不应该用于表名或字段名合并到查询中（这将导致 Psycopg 将尝试引用表名作为字符串值，从而生成无效的SQL）。如果需要生成动态SQL查询(例如动态选择表名)，可以使用 psycopg2 提供的 sql 模块:

  >>> cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # 错误
  >>> cur.execute(SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')), (10,))   # 正确