Py Postgresql

• Py Postgresql Data
• Py Postgresql Server

Summary: in this tutorial, you will learn how to connect to the PostgreSQL database server in the Python program using the psycopg database adapter.

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. Psycopg is a PostgreSQL adapter for the Python programming language. This tool allows us to connect the capabilities of the Python language and libraries to obtain, manipulate, input, and update data stored in a PostgreSQL database. At the time of this writing the current version is psycopg2. Connecting to PostgreSQL using Python. Before you can access PostgreSQL databases using Python, you must install one (or more) of the following packages in a virtual environment: psycopg2: This package contains the psycopg2 module. PyGreSQL: This package contains the pgdb module. Both of these packages support Python's portable SQL database API.

Install the psycopg2 module

First, visit the psycopg2 package here.

Second, use the following command line from the terminal:

If you have downloaded the source package into your computer, you can use the setup.py as follows:

Create a new database

First, log in to the PostgreSQL database server using any client tool such as pgAdmin or psql.

Second, use the following statement to create a new database named suppliers in the PostgreSQL database server.

Connect to the PostgreSQL database using the psycopg2

To connect to the suppliers database, you use the connect() function of the psycopg2 module.

The connect() function creates a new database session and returns a new instance of the connection class. By using the connection object, you can create a new cursor to execute any SQL statements.

To call the connect() function, you specify the PostgreSQL database parameters as a connection string and pass it to the function like this:

Or you can use a list of keyword arguments:

The following is the list of the connection parameters:

• database: the name of the database that you want to connect.
• user: the username used to authenticate.
• password: password used to authenticate.
• host: database server address e.g., localhost or an IP address.
• port: the port number that defaults to 5432 if it is not provided.

To make it more convenient, you can use a configuration file to store all connection parameters.

The following shows the contents of the database.ini file:

By using the database.ini, you can change the PostgreSQL connection parameters when you move the code to the production environment without modifying the code.

Notice that if you git, you need to add the database.ini to the .gitignore file to not committing the sensitive information to the public repo like github. The .gitignore file will be like this:

The following config() function read the database.ini file and returns connection parameters. The config() function is placed in the config.py file:

The following connect() function connects to the suppliers database and prints out the PostgreSQL database version.

How it works.

• First, read database connection parameters from the database.ini file.
• Next, create a new database connection by calling the connect() function.
• Then, create a new cursor and execute an SQL statement to get the PostgreSQL database version.
• After that, read the result set by calling the fetchone() method of the cursor object.
• Finally, close the communication with the database server by calling the close() method of the cursor and connection objects.

Execute the connect.py file

To execute the connect.py file, you use the following command:

Py Postgresql Data

You will see the following output:

It means that you have successfully connected to the PostgreSQL database server.

Py Postgresql Server

Troubleshooting

The connect() function raises the DatabaseError exception if an error occurred. To see how it works, you can change the connection parameters in the database.ini file.

For example, if you change the host to localhosts, the program will output the following message:

The following displays error message when you change the database to a database that does not exist e.g., supplier:

If you change the user to postgress, it will not be authenticated successfully as follows:

In this tutorial, you have learned how to connect to the PostgreSQL database server from Python programs.