# Python - PostgreSql Connection

###### tags: `Python` `PostgreSql` `Connection`

---

* [Python PostgreSQL Tutorial Using Psycopg2](https://pynative.com/python-postgresql-tutorial/)
* [PostgreSQL Python: Connect To PostgreSQL Database Server](https://www.postgresqltutorial.com/postgresql-python/connect/)
* [How to connect to PostgreSQL using Python](https://www.a2hosting.com/kb/developer-corner/postgresql/connecting-to-postgresql-using-python)
* [Working with PostgreSQL in Python](https://stackabuse.com/working-with-postgresql-in-python/)
* [Python PostgreSQL - Database Connection](https://www.tutorialspoint.com/python_data_access/python_postgresql_database_connection.htm)

---

# [Python PostgreSQL Tutorial Using Psycopg2](https://pynative.com/python-postgresql-tutorial/)

## Python example to connect PostgreSQL database

### Understand the PostgreSQL database connection code in detail

#### ``import psycopg2``

This line imports the Psycopg2 module in our program.
Using the classes and methods defined in this module,
we can communicate with PostgreSQL.

#### ``from psycopg2 import Error``

Using the ``Error`` class of Psycopg2, we can handle any database error and exception
while working with PostgreSQL from Python.

Using this approach, we can make our application robust.
The error class helps us to understand the error in detail.
It returns an error message and error code if any.

#### ``psycopg2.connect()``

Using the ``connect()`` method we can __create a connection to a PostgreSQL database instance__.
This returns a PostgreSQL connection object.
__This connection is thread-safe and can be shared among many threads__.

The ``connect()`` method accepts various arguments that we discussed above.

In our example, we passed the following connection arguments to connect PostgreSQL.

user = "postgres", password = "pass@#29", host = "127.0.0.1", port = "5432",database = "postgres_db"

connection instance 是 thread-safe

#### ``cursor = connection.cursor()``

We can interact with the database using the ``cursor`` class.
Using ``connection.cursor()`` we can open a cursor object
which __allows us to execute PostgreSQL command__ through Python source code.

We __can create as many cursors as we want from a single connection object__.
__Cursors created from the same connection are not isolated__,
i.e., __any changes done to the database by a cursor are immediately visible by the other cursors__.

__Cursors are not thread-safe__.

After this, we printed PostgreSQL connection properties using a connection.get_dsn_parameters().

一個 connection instance 可以建立多個 Cursors
Cursors 之間共享資訊 - 其中一個改變，其它 Cursors 可以立馬知道

#### ``cursor.execute()``

Using the cursor’s execute method we can execute a database operation or query.
Execute method takes a SQL query as a parameter.
We can retrieve query result using cursor methods
such as ``fetchone()``, ``fetchmany()``, ``fetcthall()``.
In our example, we are executing a ``SELECT version()``;
query to fetch the PostgreSQL version.

#### try-except-finally block

We placed all our code in the try-except block
to catch the database exceptions and errors that may occur during this process.

#### ``cursor.close()`` and ``connection.close()``

It is always good practice to close the cursor and connection object
__once your work gets completed to avoid database issues__.

## The mapping between Python and PostgreSQL types

There is default mapping specified to convert Python types into PostgreSQL equivalent, and vice versa. Whenever you execute a PostgreSQL query using Python following table is used by psycopg2 to return the result in the form of Python objects.

PYTHON | POSTGRESQL
--- | ---
None | NULL
bool | bool
float | real / double
int / long | smallint / integer / bigint
Decimal | numeric
str / unicode | varchar / text
date | date
time | time / timetz
datetime | timestamp / timestamptz
timedelta | interval
list | ARRAY
tuple / namedtuple | Composite types / IN syntax
dict | hstore

### Constants and numeric conversion

When you try to insert Python ``None`` and ``boolean`` values
such as ``True`` and ``False`` into PostgreSQL,
it gets converted into the proper SQL literals.
The same case is with Python numerical types.
It gets converted into equivalent PostgreSQL types.

For example, When you execute an insert query,
Python numeric objects such as ``int``, ``long``, ``float``, ``Decimal``
are converted into a PostgreSQL numerical representation.
When you read from the PostgreSQL table,
integer types are converted into an ``int``,
floating-point types are converted into a ``float``,
``numeric`` / ``Decimal`` are converted into ``Decimal``.

## Perform PostgreSQL CRUD operations from Python

Now, we created a “mobile” table.
Now let’ see how to perform insert, select, update, and delete PostgreSQL queries from Python.
In this section, We will learn how to perform PostgreSQL CRUD operations from Python.

Now, Let’s see the example.

### Working with PostgreSQL date and time in Python

This section will demonstrate how to work with
PostgreSQL ``date`` and ``timestamp`` data types in Python and vice-versa.
Most of the time, we work with ``date`` and ``time`` data.
We insert ``date`` and ``time`` into the table and
also read from it in our application whenever required.

In a usual scenario, when you execute the insert query with the ``datetime`` object,
the Python psycopg2 module converts it into a PostgreSQL ``timestamp`` format to insert it in the table.

And when you execute a SELECT query from Python
to read ``timestamp`` values from the PostgreSQL table,
the psycopg2 module converts it into a ``datetime`` object.

We are using the “Item” table for this demo.
Please copy and execute the below query on your PostgreSQL query tool
to have adequate data for this operation.

CREATE TABLE item ( 
	item_id serial NOT NULL PRIMARY KEY, 
	item_name VARCHAR (100) NOT NULL, 
	purchase_time timestamp NOT NULL,
	price INTEGER NOT NULL
);

Let’s understand this scenario with a simple example.
Here we will read ``purchase_time`` column from the PostgreSQL table
and convert it into a Python ``datetime`` object.

import datetime

import psycopg2

try:
    connection = psycopg2.connect(user="postgres",
                                  password="pynative@#29",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres_db")

    cursor = connection.cursor()
    # Executing a SQL query to insert datetime into table
    insert_query = """ INSERT INTO item (item_Id, item_name, purchase_time, price) VALUES (%s, %s, %s, %s)"""
    item_purchase_time = datetime.datetime.now()
    item_tuple = (12, "Keyboard", item_purchase_time, 150)
    cursor.execute(insert_query, item_tuple)
    connection.commit()
    print("1 item inserted successfully")

    # Read PostgreSQL purchase timestamp value into Python datetime
    cursor.execute("SELECT purchase_time from item where item_id = 12")
    purchase_datetime = cursor.fetchone()
    print("Item Purchase date is  ", purchase_datetime[0].date())
    print("Item Purchase time is  ", purchase_datetime[0].time())

except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL", error)
finally:
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

---

# Difference between psycopg2 and psycopg2-binary

初步結論與 ``wheel`` package 相容性
若安裝 和 ``Wheel`` packages 一起用沒問題，就用 ``psycopg2-binary`` package

---

## [psycopg2-binary: Why?](https://github.com/psycopg/psycopg2/issues/674)

The discussion is [here](https://www.postgresql.org/message-id/CA%2Bmi_8bd6kJHLTGkuyHSnqcgDrJ1uHgQWvXCKQFD3tPQBUa2Bw%40mail.gmail.com)

The explanation is [here]((https://www.psycopg.org/articles/2018/02/08/psycopg-274-released/))

If you have another solution let us know in the mailing list.
The solution should involve not segfaulting.

---

## [Psycopg 2.7.4 released](https://www.psycopg.org/articles/2018/02/08/psycopg-274-released/)

Posted by Daniele Varrazzo on 2018-02-08
Tagged as news, release

We have released ``Psycopg`` version 2.7.4,
bringing a few bug fixes... and
working out the problem with ``Wheel`` packages.

What's the problem with Wheels?

``Wheel`` packages are a __Python standard
to distribute self-contained binary package__.
They are especially convenient
for packages containing C extensions, such as Psycopg,
and for packages depending on external C libraries... such as Psycopg,
because the __package will contain
a binary, pre-compiled, version of the C extension
and all the depending libraries__ (excluding a list of libraries
expected to be found on any system and with a versioned ABI, such as libc).
Since the release of the ``wheel`` packages with ``psycopg`` 2.7
it has been possible to install ``Psycopg`` without
the prerequisites of a C compiler and
of Python/Postgres dev packages at build time,
and the need of a system ``libpq`` at runtime.

Unfortunately, after the packages were released,
it was reported of occasional segfaults of Python processes
using ``Psycopg`` from the ``Wheel`` package.
This was traced to the use of the Python ssl library concurrently
with Psycopg opening connections,
for instance in a multithread program
opening concurrently https resources and database connections.
The problem seems caused by a non-reentrant OpenSSL initialization function
(unfortunately invoked by libpq at every connection) and
the fact that the Python process ends up
binding two different versions of the libssl library:
the system one via the Python ssl library (e.g. imported by urllib or requests)
and the ``Wheel`` one imported by the ``libpq``,
in turn imported by ``psycopg2``.

While the problem doesn't affect many users,
a library behaving unreliably in combination with
part of the ``stdlib`` is a situation less than optimal.
The workaround is to force installing Psycopg from source, but this must be specified explicitly in the project dependencies (e.g. using the --no-binary flag in the pip command line or in the requirements.txt file); the Python packaging system doesn't really have a way to declare something like "install a package preferably from source"... so we had to create one ourselves.

Starting with Psycopg 2.7.4, we are releasing two different packages on PyPI: psycopg2 and psycopg2-binary. The latter is a Wheels-only package, with a behaviour identical to the classic one – the different name is used only in installation (it is installed by pip install psycopg2-binary, but still imported with import psycopg2 in Python).

For the lifespan of the Psycopg 2.7 cycle,
the ``psycopg2`` PyPI package will still contain wheel packages,
but starting from ``Psycopg`` 2.8
it will become again a source-only package.
Starting from Psycopg 2.7.4,
if the package is installed as binary from the psycopg2 PyPI entry,
a warning will be reported on import:

The ``psycopg2 wheel`` package will be renamed from release 2.8;
in order to keep installing from binary
please use "pip install psycopg2-binary" instead.
For details see: </docs/install.html#binary-install-from-pypi>.

The choices for the users are then two:

* if the program works fine with the ``Wheel`` packages,
and the convenience of the binary distribution is preferred,
it is possible to specify the dependency on the binary package
using the ``psycopg2-binary`` instead of the ``psycopg2`` PyPI package.
No change to the program code is needed;
* if there are concerns about the unreliability of the Wheels package,
it is adviced to force installation from source.
This requires the presence of build tools and
runtime libraries on the client,
but again it requires no change to the code.
We hope this solution will suggest
the default use of a reliable version of the library,
while still allowing the convenience of a dependencies-free package.
Feedback is welcome on the mailing list.

和 ``Wheel`` packages 一起用沒問題，就用 ``psycopg2-binary`` package