First Steps with PyGreSQL

In this small tutorial we show you the basic operations you can perform with both flavors of the PyGreSQL interface. Please choose your flavor:

First Steps with the classic PyGreSQL Interface

Before doing anything else, it’s necessary to create a database connection.

To do this, simply import the DB wrapper class and create an instance of it, passing the necessary connection parameters, like this:

>>> from pg import DB
>>> db = DB(dbname='testdb', host='pgserver', port=5432,
...     user='scott', passwd='tiger')

You can omit one or even all parameters if you want to use their default values. PostgreSQL will use the name of the current operating system user as the login and the database name, and will try to connect to the local host on port 5432 if nothing else is specified.

The db object has all methods of the lower-level Connection class plus some more convenience methods provided by the DB wrapper.

You can now execute database queries using the DB.query() method:

>>> db.query("create table fruits(id serial primary key, name varchar)")

You can list all database tables with the DB.get_tables() method:

>>> db.get_tables()
['public.fruits']

To get the attributes of the fruits table, use DB.get_attnames():

>>> db.get_attnames('fruits')
{'id': 'int', 'name': 'text'}

Verify that you can insert into the newly created fruits table:

>>> db.has_table_privilege('fruits', 'insert')
True

You can insert a new row into the table using the DB.insert() method, for example:

>>> db.insert('fruits', name='apple')
{'name': 'apple', 'id': 1}

Note how this method returns the full row as a dictionary including its id column that has been generated automatically by a database sequence. You can also pass a dictionary to the DB.insert() method instead of or in addition to using keyword arguments.

Let’s add another row to the table:

>>> banana = db.insert('fruits', name='banana')

Or, you can add a whole bunch of fruits at the same time using the Connection.inserttable() method. Note that this method uses the COPY command of PostgreSQL to insert all data in one batch operation, which is much faster than sending many individual INSERT commands:

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
>>> data = list(enumerate(more_fruits, start=3))
>>> db.inserttable('fruits', data)

We can now query the database for all rows that have been inserted into the fruits table:

>>> print(db.query('select * from fruits'))
id|   name
--+----------
 1|apple
 2|banana
 3|cherimaya
 4|durian
 5|eggfruit
 6|fig
 7|grapefruit
(7 rows)

Instead of simply printing the Query instance that has been returned by this query, we can also request the data as list of tuples:

>>> q = db.query('select * from fruits')
>>> q.getresult()
... [(1, 'apple'), ..., (7, 'grapefruit')]

Instead of a list of tuples, we can also request a list of dicts:

>>> q.dictresult()
[{'id': 1, 'name': 'apple'}, ..., {'id': 7, 'name': 'grapefruit'}]

You can also return the rows as named tuples:

>>> rows = q.namedresult()
>>> rows[3].name
'durian'

In PyGreSQL 5.1 and newer, you can also use the Query instance directly as an iterable that yields the rows as tuples, and there are also methods that return iterables for rows as dictionaries, named tuples or scalar values. Other methods like Query.one() or Query.onescalar() return only one row or only the first field of that row. You can get the number of rows with the len() function.

Using the method DB.get_as_dict(), you can easily import the whole table into a Python dictionary mapping the primary key id to the name:

>>> db.get_as_dict('fruits', scalar=True)
{1: 'apple', 2: 'banana', 3: 'cherimaya', 4: 'durian', 5: 'eggfruit',
 6: 'fig', 7: 'grapefruit', 8: 'apple', 9: 'banana'}

To change a single row in the database, you can use the DB.update() method. For instance, if you want to capitalize the name ‘banana’:

>>> db.update('fruits', banana, name=banana['name'].capitalize())
{'id': 2, 'name': 'Banana'}
>>> print(db.query('select * from fruits where id between 1 and 3'))
id|  name
--+---------
 1|apple
 2|Banana
 3|cherimaya
(3 rows)

Let’s also capitalize the other names in the database:

>>> db.query('update fruits set name=initcap(name)')
'7'

The returned string ‘7’ tells us the number of updated rows. It is returned as a string to discern it from an OID which will be returned as an integer, if a new row has been inserted into a table with an OID column.

To delete a single row from the database, use the DB.delete() method:

>>> db.delete('fruits', banana)
1

The returned integer value 1 tells us that one row has been deleted. If we try it again, the method returns the integer value 0. Naturally, this method can only return 0 or 1:

>>> db.delete('fruits', banana)
0

Of course, we can insert the row back again:

>>> db.insert('fruits', banana)
{'id': 2, 'name': 'Banana'}

If we want to change a different row, we can get its current state with:

>>> apple = db.get('fruits', 1)
>>> apple
{'name': 'Apple', 'id': 1}

We can duplicate the row like this:

   >>> db.insert('fruits', apple, id=8)
   {'id': 8, 'name': 'Apple'}

To remove the duplicated row, we can do::

   >>> db.delete('fruits', id=8)
   1

Finally, to remove the table from the database and close the connection:

>>> db.query("drop table fruits")
>>> db.close()

For more advanced features and details, see the reference: pg — The Classic PyGreSQL Interface

First Steps with the DB-API 2.0 Interface

As with the classic interface, the first thing you need to do is to create a database connection. To do this, use the function pgdb.connect() in the pgdb module, passing the connection parameters:

>>> from pgdb import connect
>>> con = connect(database='testdb', host='pgserver:5432',
...     user='scott', password='tiger')

As in the classic interface, you can omit parameters if they are the default values used by PostgreSQL.

To do anything with the connection, you need to request a cursor object from it, which is thought of as the Python representation of a database cursor. The connection has a method that lets you get a cursor:

>>> cursor = con.cursor()

The cursor has a method that lets you execute database queries:

>>> cursor.execute("create table fruits("
...     "id serial primary key, name varchar)")

You can also use this method to insert data into the table:

>>> cursor.execute("insert into fruits (name) values ('apple')")

You can pass parameters in a safe way:

>>> cursor.execute("insert into fruits (name) values (%s)", ('banana',))

To insert multiple rows at once, you can use the following method:

>>> more_fruits = 'cherimaya durian eggfruit fig grapefruit'.split()
>>> parameters = [(name,) for name in more_fruits]
>>> cursor.executemany("insert into fruits (name) values (%s)", parameters)

The cursor also has a Cursor.copy_from() method to quickly insert large amounts of data into the database, and a Cursor.copy_to() method to quickly dump large amounts of data from the database, using the PostgreSQL COPY command. Note however, that these methods are an extension provided by PyGreSQL, they are not part of the DB-API 2 standard.

Also note that the DB API 2.0 interface does not have an autocommit as you may be used from PostgreSQL. So in order to make these inserts permanent, you need to commit them to the database:

>>> con.commit()

If you end the program without calling the commit method of the connection, or if you call the rollback method of the connection, then the changes will be discarded.

In a similar way, you can update or delete rows in the database, executing UPDATE or DELETE statements instead of INSERT statements.

To fetch rows from the database, execute a SELECT statement first. Then you can use one of several fetch methods to retrieve the results. For instance, to request a single row:

>>> cursor.execute('select * from fruits where id=1')
>>> cursor.fetchone()
Row(id=1, name='apple')

The result is a named tuple. This means you can access its elements either using an index number as for an ordinary tuple, or using the column name as for access to object attributes.

To fetch all rows of the query, use this method instead:

>>> cursor.execute('select * from fruits')
>>> cursor.fetchall()
[Row(id=1, name='apple'), ..., Row(id=7, name='grapefruit')]

The output is a list of named tuples.

If you want to fetch only a limited number of rows from the query:

>>> cursor.execute('select * from fruits')
>>> cursor.fetchmany(2)
[Row(id=1, name='apple'), Row(id=2, name='banana')]

Finally, to remove the table from the database and close the connection:

>>> db.execute("drop table fruits")
>>> cur.close()
>>> con.close()

For more advanced features and details, see the reference: pgdb — The DB-API Compliant Interface