Inserting Data Into a PostgreSQL Database

Programmer at desk
vgajic / Getty Images
01
of 07

Psycopg: Install and Import

The module we will use for this tutorial is psycopg. It is available at this link. Download and install it using the directions that come with the package.

Once it is installed, you can import it like any other module:


# libs for database interface
import psycopg

If any of your fields require a date or time, you will also want to import the datetime module, which comes standard with Python.


import datetime

02
of 07

Python to PostgreSQL: Open Sesame

To open a connection to a database, psycopg needs two arguments: the name of the database ('dbname') and the name of the user ('user'). The syntax for opening a connection follows this format:


<variable name for connection> = psycopg.connect('dbname=<dbname>', 'user=<user>')

For our database, we shall use the database name 'Birds' and the username 'robert'. For the connection object within the program, let's use the variable 'connection'. So, our connection command will read as follows:


connection = psycopg.connect('dbname=Birds', 'user=robert')

Naturally, this command will only work if both variables are accurate: there must be a real database named 'Birds' to which a user named 'robert' has access. If either of these conditions are not filled, Python will throw an error.

03
of 07

Mark Your Place in PostgreSQL With Python

Next, Python likes to be able to keep track of where it last left off in reading and writing to the database. In psycopg, this is called the cursor, but we will use the variable 'mark' for our program. So, we can then construct the following assignment:


mark = connection.cursor()

04
of 07

Separating PostgreSQL Form and Python Function

While some SQL insertion formats allow for understood or unstated column structure, we will be using the following template for our insert statements:

 INSERT INTO <table> (columns) VALUES (values) ; 

While we could pass a statement in this format to the psycopg method 'execute' and so insert data into the database, this quickly becomes convoluted and confusing. A better way is to compartmentalize the statement separately from the 'execute' command as follows:

 statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')' 
 mark.execute(statement) 

In this way, form is kept separate from function. Such separation often helps in debugging.

05
of 07

Python, PostgreSQL, and the 'C' Word

Finally, after passing the data to PostgreSQL, we must commit the data to the database:

 connection.commit() 

Now we have constructed the basic parts of our function 'insert'. Put together, the parts look like this:

 connection = psycopg.connect('dbname=Birds', 'user=robert') 
 mark = connection.cursor() 
 statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')' 
 mark.execute(statement) 
 connection.commit() 

06
of 07

Define the Parameters

You will notice that we have three variables in our statement: table, columns, and values. These thus become the parameters with which the function is called:


def insert(table, columns, values):

We should, of course, follow that with a doc string:


'''Function to insert the form data 'values' into table 'table'
according to the columns in 'column' '''

07
of 07

Put it All Together And Call It

Finally, we have a function for inserting data into a table of our choice, using columns and values defined as needed.

 def insert(table, columns, values):
 '''Function to insert the form data 'values' into table 'table'
 according to the columns in 'column' '''
 
      connection = psycopg.connect('dbname=Birds', 'user=robert')
      mark = connection.cursor()
      statement = 'INSERT INTO ' + table + ' (' + columns + ') VALUES (' + values + ')'
      mark.execute(statement)
      connection.commit()
      return 

To call this function, we simply need to define the table, columns, and values and pass them as follows:

 type = "Owls"
 fields = "id, kind, date"
 values = "17965, Barn owl, 2006-07-16"
 
 insert(type, fields, values)