一、概述
>>> import psycopg2
# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")
# Open a cursor to perform database operations
>>> cur = conn.cursor()
# Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
... (100, "abc'def"))
# Query the database and obtain data as Python objects
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")
# Make the changes to the database persistent
>>> conn.commit()
# Close communication with the database
>>> cur.close()
>>> conn.close()
主要的几点:
- 链接数据库用 connect()函数,创建一个数据库链接,并返回一个 connection实例
- connection类 有如下使用方法:
-
- 创建一个指针cursors,用指针来执行数据库的查询或者其他命令。
- 当一个修改数据库的命令(如,insert,update,creat等等)完成后,要使用commit()使之在数据库中生效。
- The class cursor allows interaction with the database:
- 使用指针类 cursor可以实现与数据库的交互操作:
- 向数据库发送SQL语句,要使用execute()或者executemany().在括号中填入SQL语句,具体见下文。
- 使用 fetchone() fetchall()和fetchmany()查看SQL的结果
二、class connection
conn=psycopg2.connect( host = 'localhost', port = 5432, user = 'postgres', password = 'XXXX', database = 'XXXX' )
cursor()
commit()在对数据库进行修改后,必须在最后用下这个命令进行提交修改,否则将会丢失对数据库的操作。有部分内容是autocommit (自动提交,如creat())
rollback() 功能和commit相反。
三、class cursor
execute(operation[, parameters])
>>> cur.execute("SELECT * FROM test;")
>>> for record in cur:
... print record
...
(1, 100, "abc'def")
(2, None, 'dada')
(3, 42, 'bar')
fetchone()返回一条记录
>>> cur.execute("SELECT * FROM test WHERE id = %s", (3,))
>>> cur.fetchone()
(3, 42, 'bar')
fetchmany
(
[
size=cursor.arraysize
]
)
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchmany(2)
[(1, 100, "abc'def"), (2, None, 'dada')]
>>> cur.fetchmany(2)
[(3, 42, 'bar')]
>>> cur.fetchmany(2)
[]
fetchall
(
)
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchall()
[(1, 100, "abc'def"), (2, None, 'dada'), (3, 42, 'bar')]
目前来看,用connection链接数据库,通过cursor.excecute("SQL"),用fetchall返回结果。