Py Postgresql
There are several Python drivers for PostgreSQL. This is the incomplete feature matrix for them; please help complete it as you see fit.
See full list on tutorialspoint.com. Psycopg is the most popular PostgreSQL adapter for the Python programming language.Its core is a complete implementation of the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL. PostgreSQL is an amazing and modern R elational D atabase M anagement S ystem (RDBMS). PostgreSQL is also an open source database. PostgreSQL is cross platform. You can install PostgreSQL on Windows, Mac OS and Linux very easily. Dec 18, 2020 Installing Psycopg2 and use its API to access the PostgreSQL database Perform data insertion, data retrieval, data update, and data deletion through Python application. Next, it will cover PostgreSQL transaction management, connection pooling, and error-handling techniques to develop robust Python programs with PostgreSQL. Python adapter for PostgreSQL. The psycopg3 adaptation system. Posted by Daniele Varrazzo on 2020-11-24 Tagged as psycopg3, development, news The adaptation system between Python objects and PostgreSQL types is at the core of psycopg2 and psycopg3.The flexibility of the psycopg2 adaptation system provides good out-of-the-box object mapping and allows users to customise it to suit any need.
In general, Python users want to use psycopg2 unless they have a strong reason to try another driver, most of which are no longer maintained. Since DBAPI allows drivers to have different semantics, porting applications from one driver to another is non-trivial.
| Software | License | Platforms | Python versions | DB API 2.0 | Native (uses libpq) | Last Release | Notes |
|---|---|---|---|---|---|---|---|
| Psycopg2 | LGPL | Unix, Win32 | 2.6-3.6 | yes | yes | 2019 | Most popular python driver, required for most Python+Postgres frameworks |
| pg8000 | BSD | any (pure Python) | 3.3+ | yes | no | 2019 | Used by Web2Py. current updated official site |
| py-postgresql | BSD | any (pure Python) | 3.0+ | yes | no | 2018 | Pure Python with optional C accelerator modules,<br>extensive custom API. Python 3 only. |
| PyGreSQL | BSD | Unix, Win32 | 2.6 thru 3.6 | yes | yes | 2017 | The first PostgreSQL adapter for Python. Still actively maintained. |
| ocpgdb | BSD | Unix | 2.3-2.6 | yes | yes | 2010 | PG8.1+ |
| bpgsql | LGPL | any (pure Python) | 2.3-2.6 | yes | no | 2009 | labeled alpha |
| aiopg | BSD | any | 3.52+ | (ish) | Native | 2019 |
More abandoned driver projects:
- pgasync, Twisted-native driver, no updates since 2005
- PoPy, no updates since 2003 - development was merged with pygresql
- pyPgSQL, no updates since 2006
There is similar page on the Python wiki
- PostgreSQL Tutorial
- Advanced PostgreSQL
- PostgreSQL Interfaces
- PostgreSQL Useful Resources
- Selected Reading
Installation
The PostgreSQL can be integrated with Python using psycopg2 module. sycopg2 is a PostgreSQL database adapter for the Python programming language. psycopg2 was written with the aim of being very small and fast, and stable as a rock. You do not need to install this module separately because it is shipped, by default, along with Python version 2.5.x onwards.
If you do not have it installed on your machine then you can use yum command to install it as follows −
To use psycopg2 module, you must first create a Connection object that represents the database and then optionally you can create cursor object which will help you in executing all the SQL statements.
Python psycopg2 module APIs
The following are important psycopg2 module routines, which can suffice your requirement to work with PostgreSQL database from your Python program. If you are looking for a more sophisticated application, then you can look into Python psycopg2 module's official documentation.
| S. No. | API & Description |
|---|---|
| 1 | psycopg2.connect(database='testdb', user='postgres', password='cohondob', host='127.0.0.1', port='5432') This API opens a connection to the PostgreSQL database. If database is opened successfully, it returns a connection object. |
| 2 | connection.cursor() This routine creates a cursor which will be used throughout of your database programming with Python. |
| 3 | cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i.e., placeholders instead of SQL literals). The psycopg2 module supports placeholder using %s sign For example:cursor.execute('insert into people values (%s, %s)', (who, age)) |
| 4 | cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql. |
| 5 | cursor.callproc(procname[, parameters]) This routine executes a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. |
| 6 | cursor.rowcount This read-only attribute which returns the total number of database rows that have been modified, inserted, or deleted by the last last execute*(). |
| 7 | connection.commit() This method commits the current transaction. If you do not call this method, anything you did since the last call to commit() is not visible from other database connections. |
| 8 | connection.rollback() This method rolls back any changes to the database since the last call to commit(). |
| 9 | connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost! |
| 10 | cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. |
| 11 | cursor.fetchmany([size=cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter. |
| 12 | cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available. |
Connecting to Database
The following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.
Here, you can also supply database testdb as name and if database is successfully opened, then it will give the following message −
Create a Table
The following Python program will be used to create a table in previously created database −
When the above given program is executed, it will create COMPANY table in your test.db and it will display the following messages −
INSERT Operation
The following Python program shows how we can create records in our COMPANY table created in the above example −
When the above given program is executed, it will create given records in COMPANY table and will display the following two lines −
SELECT Operation
The following Python program shows how we can fetch and display records from our COMPANY table created in the above example −
When the above given program is executed, it will produce the following result −
UPDATE Operation
Py Postgresql Command
The following Python code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −
When the above given program is executed, it will produce the following result −
DELETE Operation
The following Python code shows how we can use the DELETE statement to delete any record and then fetch and display the remaining records from our COMPANY table −
Py-postgresql Example
When the above given program is executed, it will produce the following result −