psycopg2的基本使用

Basic module usage

The basic Psycopg usage is common to all the database adapters
implementing the [DB API
2.0]{.ul} protocol. Here is
an interactive session showing some of the basic commands:

>>> 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()

The main entry points of Psycopg are:

• The
  function connect() creates
  a new database session and returns a
  new connection instance.

• The
  class connection encapsulates
  a database session. It allows to:

  • create
    new cursor instances
    using
    the cursor() method
    to execute database commands and queries,

  • terminate transactions using the
    methods commit() or rollback().

• The
  class cursor allows
  interaction with the database:

  • send commands to the database using methods such
    as execute() and executemany(),

  • retrieve data from the database by
    iteration or
    using methods such
    as fetchone(), fetchmany(), fetchall().（返回的是一个由元组组成的列表）

Insert的字符串需要用单引号括上