Connecting to a Database

basic usage

Here is a basic usage example of Psycogp2

>>> import psycopg2

# Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=omm")

# 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:

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

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

• The class cursor allows interaction with the database:

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

  2. retrieve data from the database by iteration or using methods such as fetchone(), fetchmany(), fetchall().

Passing parameters to SQL queries

Psycopg converts Python variables to SQL values using their types: the Python type determines the function used to convert the object into a string representation suitable for PostgreSQL. Many standard Python types are already adapted to the correct SQL representation.

Passing parameters to an SQL statement happens in functions such as cursor.execute() by using %s placeholders in the SQL statement, and passing a sequence of values as the second argument of the function. For example the Python function call:

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

is converted into a SQL command similar to:

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

Using characters %, (, ) in the argument names is not supported.

When parameters are used, in order to include a literal % in the query you can use the %% string:

>>> cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
>>> cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

While the mechanism resembles regular Python strings manipulation, there are a few subtle differences you should care about when passing parameters to a query.

• The Python string operator % must not be used: the execute() method accepts a tuple or dictionary of values as second parameter. Never use % or + to merge values into queries:

  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct

• For positional variables binding, the second argument must always be a sequence, even if it contains a single variable (remember that Python requires a comma to create a single element tuple):

  >>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct

• The placeholder must not be quoted. Psycopg will add quotes where needed:

  >>> cur.execute("INSERT INTO numbers VALUES ('%s')", (10,)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # correct

• The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate:

  >>> cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # correct

• Only query values should be bound via this method: it shouldn’t be used to merge table or field names to the query (Psycopg will try quoting the table name as a string value, generating invalid SQL). If you need to generate dynamically SQL queries (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module:

  >>> cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # WRONG
  >>> cur.execute(SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')), (10,))   # correct