last modified July 6, 2020
Python PostgreSQL tutorial with psycopg2 module showshow to program PostgreSQL databases in Python with psycopg2 module.
- Psycopg2 library is one of the most widely used library for connecting and performing operations on PostgreSQL database. To connect to a PostgreSQL database from Python application, follow these steps. Import psycopg2 package.
- An example of psycopg2 cursor supporting prepared statements - prepare.py. @iboates this was a first experiment. The placeholder conversion was a draft, $1, $2 placeholders are not used anywhere in psycopg2.
- Installing psycopg2-binary with Python:3.6.4-alpine doesn’t work #684 (GitHub) Test-Driven Development with Python, Flask, and Docker; The best Docker base image for your Python application (July 2019).
PostgreSQL is a powerful, open source object-relational database system. It is amulti-user database management system. It runs on multiple platforms including Linux,FreeBSD, Solaris, Microsoft Windows and Mac OS X. PostgreSQL is developed by thePostgreSQL Global Development Group.
PGDialectpsycopg2 is a class within the sqlalchemy.dialects.postgresql.psycopg2 module of the SQLAlchemy project. Example 1 from Amazon Redshift SQLAlchemy Dialect. Amazon Redshift SQLAlchemy Dialect is a SQLAlchemy Dialect that can communicate with the AWS Redshift data store. The SQL is essentially PostgreSQL and requires psycopg2 to properly operate. This project and its. Psycopg2 库是 python 用来操作 postgreSQL 数据库的第三方库。使用时需要先进行安装。pip install psycopg2。 python 部分准备就绪，接下来我们先来看看 postgreSQL 的基础知识。 postgreSQL 安装. Window 下安装非常简单，到官方网站下载安装包，然后按照提示安装.
The psycopg2 module
There are several Python libraries for PostgreSQL. language. In this tutorial weuse the
psycopg2 module. It is a PostgreSQL database adapter forthe Python programming language. It is mostly implemented in C as a
We install the
Python psycopg2 version example
In the first code example, we get the version of the PostgreSQL database.
In the program we connect to the previously created
testdb database. We execute an SQL statement which returns theversion of the PostgreSQL database.
psycopg2 is a Python module which is used to workwith the PostgreSQL database.
We initialize the con variable to
None. In case we could not create a connectionto the database (for example the disk is full), we would not have a connectionvariable defined. This would lead to an error in the finally clause.
connect() method creates a new database session andreturns a connection object. The user was created without a password.On localhost, we can omit the password option. Otherwise, it mustbe specified.
From the connection, we get the cursor object. The cursor is used to traversethe records from the result set. We call the
execute() method ofthe cursor and execute the SQL statement.
We fetch the data. Since we retrieve only one record, we call the
We print the data that we have retrieved to the console.
In case of an exception, we print an error message andexit the program with an error code 1.
In the final step, we release the resources.
This is the output.
In the second example, we again get the version of the PostgreSQL database. Thistime we use the
The program returns the current version of the PostgreSQL database. With the useof the with keyword. The code is more compact.
with keyword, Python automaticallyreleases the resources. It also provides error handling.
Python psycopg2 execute
We create the
cars table and insert several rows to it.The
execute() executes a database operation (query or command).
The program creates the
cars table and inserts eight rows into thetable.
This SQL statement creates a new
cars table. The table hasthree columns.
These two lines insert two cars into the table.
We verify the written data with the
Python psycopg2 executemany
executemany() method is a convenience method for launching a database operation (query or command) against all parameter tuples or mappings found in the provided sequence. The function is mostly useful for commands that update the database: any result set returned by the query is discarded.
This example drops the
cars table if it exists and (re)creates it.
The first SQL statement drops the
cars table if it exists.The second SQL statement creates the
This is the query that we use.
We insert eight rows into the table using the convenience
executemany()method. The first parameter of this method is a parameterized SQL statement.The second parameter is the data, in the form of a tuple of tuples.
Python psycopg2 last inserted row id
psycopg2 does not support the
lastrowid attribute.To return the id of the last inserted row, we have to use PostgreSQL's
RETURNING id clause.
The program creates a new
words table and prints the Id of the last inserted row.
This is the output.
I won’t buy another Mac after my Intel ones have died or become unsupported. Apple moving to a custom ARM chip for Mac’s means that Boot Camp is no more. How to get minecraft windows 10 on mac. Parallels is working on a M1 Mac based preview but even that application will only run ARM based operating systems. So don’t count on running a X86 Windows version, possibly their ARM Windows but that is so far from perfect as a Windows OS. I think Apple switched to its chips purely on locking in users and making more profits.
Python psycopg2 fetchall
fetchall() fetches all the (remaining) rows of a query result,returning them as a list of tuples. An empty list is returned if there is nomore record to fetch.
In this example, we retrieve all data from the
This SQL statement selects all data from the
fetchall() method gets all records. It returnsa result set. Technically, it is a tuple of tuples. Each of theinner tuples represents a row in the table.
We print the data to the console, row by row.
This is the output of the example.
Python psycopg2 fetchone
fetchone() returns the next row of a query result set,returning a single tuple, or
None when no more data is available.
In this example we connect to the database and fetch the rowsof the
cars table one by one.
We access the data from the while loop. When we read the last row,the loop is terminated.
fetchone() method returns the next row fromthe table. If there is no more data left, it returns
None.In this case we break the loop.
The data is returned in the form of a tuple. Here we selectrecords from the tuple. The first is the Id, the secondis the car name and the third is the price of the car.
Python psycopg2 dictionary cursor
The default cursor retrieves the data in a tuple of tuples. Witha dictionary cursor, the data is sent in a form of Python dictionaries.We can then refer to the data by their column names.
In this example, we print the contents of the
cars tableusing the dictionary cursor.
The dictionary cursor is located in the extras module.
We create a
The data is accessed by the column names. The column names arefolded to lowercase in PostgreSQL (unless quoted) and are case sensitive.Therefore, we have to provide the column names in lowercase.
Python psycopg2 parameterized queries
When we use parameterized queries, we use placeholders instead of directlywriting the values into the statements. Parameterized queries increasesecurity and performance.
psycopg2 module supports two types of placeholders:ANSI C printf format and the Python extended format.
We update a price of one car. In this code example, we use the questionmark placeholders.
The characters (%s) are placeholders for values. The values areadded to the placeholders.
rowcount property returns the number of updatedrows. In our case one row was updated.
The price of the car was updated. We check the change with the
The second example uses parameterized statements withPython extended format.
We select a name and a price of a car using
The named placeholders start with a colon character.
This is the output.
Python psycopg2 mogrify
mogrify is a psycopg2 extension to the Python DB API thatreturns a query string after arguments binding. The returned string is exactlythe one that would be sent to the database running the
execute() method or similar.
The program shows a SELECT query string after binding the arguments with
This is the output.
Python psycopg2 insert image
In this section we are going to insert an image to thePostgreSQL database.
For this example, we create a new table called
images. For theimages, we use the
BYTEA data type. It allows to store binary strings.
In the program, we read an image from the current working directoryand write it into the
images table of the PostgreSQL
We read binary data from the filesystem. We have a JPEG imagecalled
The data is encoded using the
This SQL statement is used to insert the image into the database.
Python psycopg2 read image
In this section, we are going to perform the reverse operation.We read an image from the database table.
We read image data from the images table and write itto another file, which we call
We open a binary file in a writing mode. The datafrom the database is written to the file.
These two lines select and fetch data from the
imagestable. We obtain the binary data from the first row.
Python PostgreSQL metadata
Metadata is information about the data in the database.Metadata in a PostgreSQL database contains information about the tablesand columns, in which we store data. Number of rows affectedby an SQL statement is a metadata. Number of rows and columns returnedin a result set belong to metadata as well.
Metadata in PostgreSQL can be obtained using from the
descriptionproperty of the cursor object or from the
Next we print all rows from the
cars table with theircolumn names.
We print the contents of the
cars table to the console.Now, we include the names of the columns too.
We get the column names from the
description propertyof the cursor object.
This line prints three column names of the
We print the rows using the for loop. The datais aligned with the column names.
This is the output.
In the following example we list all tables in the
The code example prints all available tables in the current databaseto the terminal.
The table names are stored inside the system
These were the tables on our system.
Python psycopg2 export and import of data
We can export and import data using
The code example copies data from the
cars table intothe
We open a file where we write the data from the
copy_to method copies data from the
cars tableto the opened file. The columns are separated with the
These are the contents of the
Now we are going to perform a reverse operation. We importthe dumped table back into the database table.
We delete the data from the
In the program we read the contents of the
cars fileand copy it back to the cars table.
We open the
cars.csv file for reading and copy the contents to the
cars table. The changes are committed.
The output shows that we have successfullyrecreated the saved
Python psycopg2 transactions
A transaction is an atomic unit of database operations againstthe data in one or more databases. The effects of all the SQLstatements in a transaction can be either all committedto the database or all rolled back.
In psycopg2 module transactions are handled by the connection class.The first command of a connection cursor starts a transaction. (We do not need to encloseour SQL commands by
END statements tocreate a transaction. This is handled automatically by
psycopg2.) Thefollowing commands are executed in the context of this newtransaction. In case of an error, the transaction is aborted andno further commands are executed until the
The documentation to the
psycopg2 module says that the connection isresponsible to terminate its transaction, calling either the
rollback() method. The committed changes areimmediately made persistent into the database. Closing the connection using the
close() method or destroying the connection object (using del orletting it fall out of scope) will result in an implicit
psycopg2 module also supports an autocommit mode,where all changes to the tables are immediately effective.To run in autocommit mode, we set the
autocommitproperty of the connection object to
We create the
friends table and try to fill it with data. However,as we will see, the data will be not committed.
commit() method is commented. If we uncomment theline, the data will be written to the table.
finally block is always executed. If we have not committed thechanges and no error occurs (which would roll back the changes)the transaction is still opened. The connection is closed with the
close() method and the transaction isterminated with an implicit call to the
Only after we have uncommented the line, the
friends tableis created.
Python psycopg2 autocommit
In the autocommit mode, an SQL statement is executed immediately.
In this example, we connect to the database in the autocommit mode.We do not call neither
We set the connection to the autocommit mode.
The data was successfully committed to the
Visit Python tutorial or list all Python tutorials.