Inserting Data Into a PostgreSQL Database

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

= psycopg.connect('dbname=', '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 is not filled, Python will throw an error.

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

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.

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()06of 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"07of 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)

Interesting Articles

The 7 Best Online Colleges for 2019 Enrollment

July 6,2022

Was the 1959 Tibetan Uprising really an uprising?

July 6,2022

Tectonic Plates' Effect on Evolution

July 6,2022