Create A PostgreSQL Table And Insert Data Using The Psycopg2 Adapter For Python

Written by Data Pilot
September 15, 2019
  • PostgreSQL
  • Psycopg2
  • Python
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re using Python to interact with PostgreSQL, it’s important to know all the different database operations you can perform in your scripts. With the psycopg2 adapter, you can do more than just query for data– you can also create tables and add rows to them. This article will introduce the execute() method for the psycopg2 adapter for PostgreSQL, and it will show you how to use it to create a PostgreSQL table and insert data with the psycopg2 adapter for Python.

Prerequisites

Before we start working on our Python script, let’s look at the key prerequisites that are necessary for this tutorial:

  • You’ll need to have Python 3 installed, as well as the PIP package manager. Use the python3 -V and pip3 -V commands to retrieve the version numbers for your installations of Python and PIP3.

  • You can use the the pip3 package manager to install the psycopg2 library:

1
pip3 install psycopg2

Screenshot of Python 3 and PIP3 versions in terminal and installation of psycopg2

  • You’ll need to have a PostgreSQL database created on your server, and you’ll also need a user that has been granted privileges to the database. You can do this in the psql interface using the following commands:
1
2
3
CREATE DATABASE some_database;
CREATE USER some_user WITH ENCRYPTED PASSWORD 'mypass';
GRANT ALL PRIVILEGES ON DATABASE some_database TO some_user;

Import the psycopg2 modules for executing PostgreSQL statements in a Python script

Once you’ve made sure that all your prerequisites are in place, you can proceed to create your Python script. You can create it in your favorite IDE, or you can use the touch command in a UNIX terminal, making sure that your file has the .py extension. Then, import the following psycopg2 libraries at the beginning of the script:

1
2
3
4
5
# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, sql

# use a Python UUID for the table's PRIMARY KEY
import uuid

NOTE: The code shown in this article uses Python’s built-in uuid library to generate unique IDs for the table’s PRIMARY KEY column. The connect library is used for establishing a connection with PostgreSQL and for generating a cursor for the execute() method.

Connect to PostgreSQL using psycopg2’s ‘connect()’ method

1
2
3
4
5
6
7
# declare a new PostgreSQL connection object
conn = connect(
dbname = "python_test",
user = "objectrocket",
host = "localhost",
password = "mypass"
)

Be sure to pass the correct parameters when calling the connect() method, and keep in mind that the string you pass to the dbname parameter will be the database into which your new table will be inserted.

Define a function that will execute the PostgreSQL statements

The following function passes a SQL string to the cursor object’s execute() method inside a try-except indentation block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# define a function that will execute SQL statements
def execute_sql(ident=None, statement):

print ("\nexecute_sql() SQL statement:", statement)

# check if SQL statement/query end with a semi-colon
if statement[-1] != ";":
err = "execute_sql() ERROR: All SQL statements and "
err = err + "queries must end with a semi-colon."
print (err)
else:
try:
# have sql.SQL() return a sql.SQL object
sql_object = sql.SQL(
# pass SQL string to sql.SQL()
statement
).format(
# pass the identifier to the Identifier() method
sql.Identifier( ident )
)

# pass the psycopg2.sql.SQL object to execute() method
cursor.execute( sql_object )

# print message if no exceptions were raised
print ("execute_sql() FINISHED")

except Exception as err:
print ("execute_sql() ERROR:", err)

If the PostgreSQL server raises an error, it will get returned to the psycopg2 adapter and the function will print it.

Dropping a PostgreSQL table using the execute_sql() function

The following code example shows how we would pass a SQL string to the function in order to drop a table:

1
2
3
4
5
# drop the table if it already exists
sql_statement = "DROP TABLE some_table;"

# call the function to drop table
execute_sql(TABLE_NAME, sql_statement)

If the table doesn’t exist, the following response will be returned:

1
2
execute_sql() SQL statement: DROP TABLE some_table;
execute_sql() ERROR: table "some_table" does not exist

Declare a cursor object and string for the PostgreSQL table name

In the code shown below, we declare a string for the table name and instantiate a psycopg2 cursor object. The cursor will be used to execute SQL statements using the execute_sql() function we defined earlier:

1
2
3
4
5
# declare a string for the table name
TABLE_NAME = "some_table"

# instantiate a cursor object from the connection
cursor = conn.cursor()

Declare a SQL string for creating a new PostgreSQL table

Now, let’s declare a SQL statement string that will create a PostgreSQL table:

1
2
3
4
5
6
7
8
# declare a SQL string for CREATE TABLE
sql_statement = """
CREATE TABLE {} (
col_uuid VARCHAR(32) PRIMARY KEY,
col_str VARCHAR(64) UNIQUE NOT NULL,
col_int INTEGER NOT NULL,
col_bool BOOLEAN NOT NULL
);""".format( TABLE_NAME )

NOTE: A Python uuid.UUID string, converted from the uuid.uuid4() method, has a character length of 32.

Execute the PostgreSQL ‘CREATE TABLE’ transaction

We can execute the SQL string by passing it, along with the TABLE_NAME string, to the execute_sql() function:

1
2
3
4
5
# print the psycopg2.sql.SQL object
print ("CREATE TABLE sql_statement:\n", sql_statement)

# call the function to create table
execute_sql(TABLE_NAME, sql_statement)

Declare a SQL string for inserting rows of data into a PostgreSQL table

We’ll declare another SQL string that will use the INSERT INTO SQL command to insert several rows of data into the table we just instructed psycopg2 to create.

We’ll use the curly braces ({}) as substitutions to generate UUIDs for each row of data:

1
2
3
4
5
6
# declare a string that will INSERT rows into the table
sql_statement = """INSERT INTO {} (col_uuid, col_str, col_int, col_bool) VALUES
('{}', 'hello, world', 1234, true),
('{}', 'kumusta, mundo', 42, false),
('{}', '你好，世界', 4321, 'yes');"""
# string must be enclosed using single-quotes (')

All that’s left to do is to pass uuid.uuid4().hex to the string’s format() method to generate the UUIDs:

1
2
3
4
5
6
7
8
# each '{}' needs a substitution
sql_statement = sql_statement.format(
TABLE_NAME,
uuid.uuid4().hex,
uuid.uuid4().hex,
uuid.uuid4().hex
)
# string must be enclosed using single-quotes (')

Screenshot of Python IDLE using string's format to concatenate strings and getting length of UUID object

When you run your Python script, it should generate a SQL statement string that looks something like this:

1
2
3
4
INSERT INTO some_table (col_uuid, col_str, col_int, col_bool) VALUES
('5e4dbd53d357478b93610608413a3d38', 'hello, world', 1234, TRUE),
('c45e750e271a448f9c56ffcf0a5636d7', 'kumusta, mundo', 42, FALSE),
('e45235027b884dbcb5d3381be9e8a5d7', '你好，世界', 1234, 'yes');

Execute the PostgreSQL ‘INSERT INTO’ transaction

Just like we did with the CREATE TABLE string, we’ll also have to pass the INSERT INTO some_table string to the execute_sql() function:

1
2
3
4
5
# print the SQL statement
print ("INSERT INTO sql_statement:\n", sql_statement)

# call the function to insert data into table
execute_sql(TABLE_NAME, sql_statement)

Commit the PostgreSQL transactions and close the connection

Finally, we commit the transactions to the PostgreSQL server and then close the connection and cursor object:

1
2
3
4
5
6
7
8
# commit transactions to PostgreSQL
conn.commit()

# close the cursor object to avoid memory leaks
cursor.close()

# close the connection object
conn.close()

Run the Python script to create the PostgreSQL table and insert data rows

Now that we’ve finished reviewing all the parts of our script, we can try running it. Save the code in your Python script and navigate to your PostgreSQL-Python project directory. Then, run the script using the python3 command.

You should see your terminal or command prompt window print something like this:

1
2
3
4
5
execute_sql() SQL statement: INSERT INTO some_table (col_uuid, col_str, col_int, col_bool) VALUES
('5e4dbd53d357478b93610608413a3d38', 'hello, world', 1234, true),
('c45e750e271a448f9c56ffcf0a5636d7', 'kumusta, mundo', 42, false),
('e45235027b884dbcb5d3381be9e8a5d7', '你好，世界', 1234, 'yes');
execute_sql() FINISHED

Screenshot of a Python script running in terminal to create a PostgreSQL table and insert rows of data

Use ‘psql’ to verify that the data rows were inserted into the PostgreSQL tablePostgreSQL

We can verify that both the PostgreSQL table and its data were created in Python by going back into the database using the psql interface. Use the following SQL statements to get all the rows from the new table you just created:

1
2
\dt
SELECT * FROM some_table;

Screenshot of psql listing tables in a PostgreSQL database and selecting rows of data

Conclusion

There are many ways to interact with PostgreSQL and execute various database operations. One common method is to connect to PostgreSQL via a Python script, using the psycopg2 package. In this tutorial, we showed you how to create a table and insert data using the psycopg2 adapter for Python. If you follow along with the examples we provided, you’ll be able to create your own Python script and execute whatever database transactions you need.

Just the Code

We looked at quite a bit of code throughout this tutorial. Shown below is the sample Python script in its entirety:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import the psycopg2 database adapter for PostgreSQL
from psycopg2 import connect, sql

# use a Python UUID for the table's PRIMARY KEY
import uuid

# declare a new PostgreSQL connection object
conn = connect(
dbname = "python_test",
user = "objectrocket",
host = "localhost",
password = "mypass"
)

# define a function thPostgreSQLatPostgreSQL will execute SQL statements
def execute_sql(ident, sPostgreSQLtatement):
PostgreSQL
print ("\nexecute_sqPostgreSQLl() SQL statement:", statement)
PostgreSQL
# check if SQL statePostgreSQLment/query end with a semi-colon
if statement[-1] != PostgreSQL";":
err = "execute_sPostgreSQLql() ERROR: All SQL statements and "
err = err + "quePostgreSQLries must end with a semi-colon."
print (err)PostgreSQL
else:PostgreSQL
try:PostgreSQL
# have sql.SPostgreSQLQL() return a sql.SQL object
sql_object =PostgreSQL sql.SQL(
# pass SPostgreSQLQL string to sql.SQL()
statemenPostgreSQLt
).format(PostgreSQL
# pass tPostgreSQLhe identifier to the Identifier() method
sql.IdenPostgreSQLtifier( ident )
)PostgreSQL
PostgreSQL
# pass the pPostgreSQLsycopg2.sql.SQL object to execute() method
cursor.execuPostgreSQLte( sql_object )

# print message if no exceptions were raised
print ("execute_sql() FINISHED")

except Exception as err:
print ("execute_sql() ERROR:", err)

# declare a string for the table name
TABLE_NAME = "some_table"

# instantiate a cursor object from the connection
cursor = conn.cursor()

# drop the table if it already exists
sql_statement = "DROP TABLE some_table;"

# call the function to drop table
execute_sql(TABLE_NAME, sql_statement)

# declare a SQL string for CREATE TABLE
sql_statement = """
CREATE TABLE {} (
col_uuid VARCHAR(32) PRIMARY KEY,
col_str VARCHAR(64) UNIQUE NOT NULL,
col_int INTEGER NOT NULL,
col_bool BOOLEAN NOT NULL
);""".format( TABLE_NAME )

# print the psycopg2.sql.SQL object
print ("CREATE TABLE sql_statement:\n", sql_statement)

# call the function to create table
execute_sql(TABLE_NAME, sql_statement)


# "{} and {}".format("pancakes", "syrup")
# declare a string that will INSERT rows into the table
sql_statement = """INSERT INTO {} (col_uuid, col_str, col_int, col_bool) VALUES
('{}', 'hello, world', 1234, true),
('{}', 'kumusta, mundo', 42, false),
('{}', '你好，世界', 4321, 'yes');"""
# string must be enclosed using single-quotes (')

# each '{}' needs a substitution
sql_statement = sql_statement.format(
TABLE_NAME,
uuid.uuid4().hex,
uuid.uuid4().hex,
uuid.uuid4().hex
)

# print the psycopg2.sql.SQL object
print ("CREATE TABLE sql_statement:\n", sql_statement)

# call the function to insert data into table
execute_sql(TABLE_NAME, sql_statement)

# commit transactions to PostgreSQL
conn.commit()

# close the cursor object to avoid memory leaks
cursor.close()

# close the connection object
conn.close()
Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.