Python has a huge support for relational databases from Mysql, Postgres, Sqlite3, Oracle and much more others. Postgres has gained lots of popularity of recent and thus the spring of lots of python library, some of this python binding for postgres are backed by huge community and supported by lots of python web frameworks commonly django and more.
Python bindings for PostgresSQL
- Psycopg2. Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection). It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent “INSERT”s or “UPDATE”s. Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being both efficient and secure.
- pg8000 is a pure-Python PostgreSQL driver that complies with DB-API 2.0. It is tested on Python versions 3.5+, on CPython and PyPy, and PostgreSQL versions 9.4+. Pg8000’s name comes from the belief that it is probably about the 8000th PostgreSQL interface for Python.
- py-postgresql is a Python 3 package providing modules to work with PostgreSQL.This includes a high-level driver, and many other tools that support a developer working with PostgreSQL databases.
- PyGreSQL PyGreSQL is an open-source Python module that interfaces to a PostgreSQL database. It embeds the PostgreSQL query library to allow easy use of the powerful PostgreSQL features from a Python scriptons.
$ pip install psycopg2
Obtain a stand-alone package, not requiring a compiler or external libraries, by installing the psycopg2-binary package from PyPI
$ pip install psycopg2-binary
How to Connect PostgreSQL Database using Psycopg2
To connect the PostgreSQL database and perform SQL queries you must know the database name you want to connect. If you have not created any database.
conn = psycopg2.connect("dbname=test user=postgres") cur = conn.cursor() cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);") cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "abc'def")) cur.execute("SELECT * FROM test;") cur.fetchone() (1, 100, "abc'def")
# Make changes permenant
# Close communication with the database