Day42: Python to Postgres

I want to do three things: (1) create a PostgreSQL database to store data, (2) use Python to create a table in the database, and (3) use Python to insert data into the table of the database.

Aim 1. Creating the database

We first start the PostgreSQL server and then create the database (which we called “testdb”).

# init Postgres
initdb postgres

# start the postgres server
pg_ctl -D postgres start

# create your database
createdb testdb

After the server is started and the database is created, we can then use Python to connect to the PostgreSQL database.

Connecting Python to PostgreSQL

Psycopg is the most popular PostgreSQL adapter for the Python programming

import psycopg2

# establish connection
conn = psycopg2.connect(dbname='testdb')

# creates cursor to be used throughout program
cur = conn.cursor()

The “PostgreSQL - Python Interface” tutorial is a good place to start learning how psycopg2 works

Aim 2. Creating a table

In creating a table, we first define the SQL create table command and then execute it.

my_table = "newtable"

command = """
    CREATE TABLE {table} (
        color_id SERIAL PRIMARY KEY,
        color_name VARCHAR(255) NOT NULL
    )""".format(table=my_table)

cur.execute(command)

PostgreSQL Python: Create Tables also demonstrates how to create new tables in the PostgreSQL database using Python.

Aim 3. Inserting data into table

Once the table is created, we can now insert data one row row at a time.

The cursor.execute(sql [, optional parameters]) routine executes an SQL statement. The SQL statement may be parameterized using %s sign placeholder instead of SQL literals. For example: cursor.execute("insert into people values (%s, %s)", (who, age))

sql = """
    INSERT INTO {table}(color_name)
    VALUES(%s) RETURNING color_id;
    """.format(table=my_table)

# execute the INSERT statement
cur.execute(sql, ("red",))

Data can also be inserted many rows at a time.

The curosr.executemany(sql, seq_of_parameters) routine executes an SQL command against all parameter sequences or mappings found in the sequence sql.

vendor_names = [
    ('orange',),
    ('yellow',),
    ('green',),
    ('blue',),
    ('indigo',),
    ('violet',)
]
cur.executemany(sql, vendor_names)

Selecting data

To select the data, we fetch it:

The cursor.fetchall() routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.

cur.execute("SELECT * from {table}".format(table=my_table))

rows = cur.fetchall()
for row in rows:
    print(row)

#>  (1, 'red')
#>  (2, 'orange')
#>  (3, 'yellow')
#>  (4, 'green')
#>  (5, 'blue')
#>  (6, 'indigo')
#>  (7, 'violet')

Commit changes

Once our changes have been made, we than commit to finalize them:

The connection.commit() method commits the current transaction. If you don’t call this method, anything you did since the last call to commit() is not visible from other database connections.

# close communication with the PostgreSQL database server
cur.close()

# commit the changes
conn.commit()

# close connection
conn.close()

Using the PostgreSQL interactive terminal (ie. psql), we find the transactions have indeed been committed.

Finally, you can stop the PostgreSQL server with:

pg_ctl -D postgres stop

It’s amazing what a good night sleep can do. -- csiu