- \n
- Psycopg2<\/li>\n
- py-PostgreSQL<\/li>\n
- ocpgdb<\/li>\n
- PyGreSQL<\/li>\n
- pg8000<\/li>\n<\/ul>\n
1.1 Setting up Python<\/h3>\n
If someone needs to go through the Python installation on Windows, please watch this<\/a> link. You can download the Python from this<\/a> link.<\/p>\n
1.2 Setting up Psycopg2<\/h3>\n
Once the python is successfully installed on your system you can install the psycopg2<\/strong> using a simple
pip<\/code> command. You can fire the below command from the command prompt and it will successfully download the module from pypi.org<\/a> and install it.<\/p>\nInstallation command<\/em><\/span><\/p>\n
pip install psycopg2\n<\/pre>\n
1.3 Setting up Postgres database<\/h3>\n
To start with the tutorial, I am hoping that you have the Postgres up and running in your localhost environment. For easy setup, I have the database up and running on the Docker environment. You can execute the below command to get the container running on docker in minutes.<\/p>\n
Docker command<\/em><\/span><\/p>\n
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD=password --name postgres postgres\n<\/pre>\n
If everything goes well the container would be started successfully as shown in Fig. 1. You can use the
docker ps -a<\/code> command to confirm that container is started successfully. For further information on docker basics, you can navigate to this tutorial<\/a>.<\/p>\n\n![\"Python](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/02\/docker-postgres-container-img1-1.jpg\")
<\/a>Fig. 1: Postgres container on Docker<\/figcaption><\/figure>\n<\/div>\n 2. Python PostgreSQL CRUD Operations Example<\/h2>\n
Before going any deeper in the practical let me walk you through a simple architecture diagram where it shows that wherein the psycopg2 module fits in the picture.<\/p>\n
\n![\"Python](\"https:\/\/examples.javacodegeeks.com\/wp-content\/uploads\/2021\/02\/python-psycopg2-postgres-application-flowchart-1.jpeg\")
<\/a>Fig. 2: Python postgresql connection<\/figcaption><\/figure>\n<\/div>\n For the psycopg2 module to work we will supply the following attributes to the PostgreSQL from the python application i.e.<\/p>\n
- \n
- user<\/strong> \u2013 Identity user to work with the PostgreSQL database. Default PostgreSQL user is
postgres<\/code><\/li>\n- password<\/strong> \u2013 Credential of the identity user<\/li>\n
- host<\/strong> \u2013 Address of the database server. If running on localhost then you can either use localhost or 127.0.0.1<\/li>\n
- port<\/strong> – The port number. Default PostgreSQL port is
5432<\/code><\/li>\n- database<\/strong> \u2013 Database to which you want to connect. It can be left blank<\/li>\n<\/ul>\n
Let us dive in with the programming stuff now.<\/p>\n
2.1 Creating a configuration file<\/h3>\n
Add the following code to the environment file wherein we will specify the connection and database details. You are free to change these details as per your configuration setup.<\/p>\n
local.env<\/em><\/span><\/p>\n
[DB]\nusername = postgres\npassword = password\nhost = localhost\nport = 5433\ndatabase =\n<\/pre>\n
2.2 Creating a python script to read the configuration<\/h3>\n
Add the following code to the python script which will read the configuration file created above and return the config object to be used later while connecting to the database. The script will import the
configparser<\/code> module for reading the configuration file. Remember to give the correct path where thelocal.env<\/code> is created.<\/p>\nreaddbconfig.py<\/em><\/span><\/p>\n
import configparser\ndef read_db_params():\n # reading the env file\n config = configparser.ConfigParser()\n config.read('config\/local.env')\n return config\n<\/pre>\n2.3 Connecting to the database<\/h3>\n
Add the following code to the python script which will connect to the postgresql with the help of the
psycopg2<\/code> module. If the connection is successful the script will print the database version and if not will throw an error and print the exception message on the console. The python script consists of \u2013<\/p>\n- \n
connect()<\/code> method for creating a connection to the postgresql<\/li>\ncursor()<\/code> method to interact with the database and perform operations<\/li>\nexecute()<\/code> method to run a database query<\/li>\nfetchone()<\/code> method to return a single row<\/li>\nclose()<\/code> method to close the cursor and connector object once the work gets done and avoid connection leak issues<\/li>\n<\/ul>\nconnecttodb.py<\/em><\/span>
<\/div> <\/div><\/p>\n# python postgresql\nimport psycopg2\nfrom psycopg2 import Error\nfrom readdbconfig import *\ndef connect(database_name):\n try:\n # method will read the env file and return the config object\n params = read_db_params()\n db_name = params.get('DB', 'database') if not database_name else database_name\n # connect to database\n # reading the database parameters from the config object\n conn = psycopg2.connect(\n user=params.get('DB', 'username'),\n password=params.get('DB', 'password'),\n host=params.get('DB', 'host'),\n port=params.get('DB', 'port'),\n database=db_name\n )\n return conn\n except(Exception, Error) as error:\n print(error)\ndef print_version(conn):\n # creating a cursor to perform database operations\n cursor = conn.cursor()\n try:\n # execute the sql query\n cursor.execute('SELECT version();')\n # fetch result\n record = cursor.fetchone()\n print('PostgreSQL version = {}'.format(record))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed.')\n# driver code\nif __name__ == '__main__':\n # ops method\n print_version(connect(''))\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console. If not the exception message will be shown.<\/p>\n
Logs<\/em><\/span><\/p>\n
PostgreSQL version = ('PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit',)\n\nDatabase connection closed.\n<\/pre>\n2.4 Create table<\/h3>\n
Add the following code to the python script which will create a table<\/span> in the
employee<\/code> database. The python script will create a table if it does not exists. If it does the script will throw an error. Ensure that the database already exists<\/em>.<\/p>\ncreatetable.py<\/em><\/span><\/p>\n
from connecttodb import *\n# create table method\ndef create_table(conn):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n # sql query\n query = '''\n CREATE TABLE employee (id INT NOT NULL, first_name VARCHAR(255), \n last_name VARCHAR(100), email VARCHAR(150), \n gender VARCHAR(50), phone VARCHAR(100), \n PRIMARY KEY (id));\n '''\n try:\n # execute the command\n cursor.execute(query)\n # commit the changes\n conn.commit()\n print('Table created successfully')\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed')\n# driver code\nif __name__ == '__main__':\n # connect to database and create table\n create_table(connect('employee'))\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console. If not the exception message will be shown.<\/p>\n
Logs<\/em><\/span><\/p>\n
Table created successfully\n\nDatabase connection closed.\n<\/pre>\n
2.5 Insert data into the table<\/h3>\n
Add the following code to the python script which will insert the default employee-related data<\/span> to the
employee<\/code> table. The python script will check if default data is present in the table or not. If present<\/em> it will skip the insert operation. If not<\/em> the default data will be inserted.<\/p>\ninsertdata.py<\/em><\/span><\/p>\n
from connecttodb import *\nfrom helper import *\n# insert data\ndef insert(conn):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n # sql query\n query = '''\n INSERT INTO employee (id, first_name, last_name, email, gender, phone) VALUES (%s, %s, %s, %s, %s, %s);\n '''\n try:\n count = get_records_count(cursor)\n if count > 0:\n print('Default data present. Skipping insert')\n else:\n data = [\n (1, 'Marga', 'Cronchey', 'mcronchey0@pen.io', 'F', '314-289-7265'),\n (2, 'Theda', 'Mushrow', 'tmushrow1@whitehouse.gov', 'F', '804-163-9834'),\n (3, 'Marielle', 'Bonicelli', 'mbonicelli2@sitemeter.com', 'F', '624-922-2416'),\n (4, 'Locke', 'Watkinson', 'lwatkinson3@accuweather.com', 'M', '456-260-1052'),\n (5, 'Blakelee', 'Wilcot', 'bwilcot4@twitpic.com', 'M', '608-344-4090')\n ]\n # execute the command\n cursor.executemany(query, data)\n # commit the changes\n conn.commit()\n print('{} records inserted'.format(cursor.rowcount))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed')\n# driver code\nif __name__ == '__main__':\n # connect to database and insert data into the table\n insert(connect('employee'))\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console.<\/p>\n
Logs<\/em><\/span><\/p>\n
5 records inserted\n\nDatabase connection closed.\n<\/pre>\n
2.6 Get all data from the table<\/h3>\n
Add the following code to the python script which will get all the employee-related data<\/span> from the
employee<\/code> table. The python script will check if any existing data is present in the table or not. If present<\/em> it will print the data on the console. If not<\/em> the following message –No data present in db<\/code> will be printed on the console.<\/p>\ngetall.py<\/em><\/span><\/p>\n
from connecttodb import *\nfrom helper import *\n# get all records\ndef get_all(conn):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n # sql query\n query = '''SELECT * FROM employee;'''\n try:\n count = get_records_count(cursor)\n if count == 0:\n print('No data present in db')\n else:\n # execute the command\n cursor.execute(query)\n records = cursor.fetchall()\n print('EMPLOYEE INFORMATION')\n print('-------------------------------------')\n for record in records:\n full_name = record[1] + \" \" + record[2]\n print('Id = {}, Name = {}, Email = {}, Gender = {}, Phone = {}'.format(record[0], full_name,\n record[3], record[4], record[5]))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed')\n# driver code\nif __name__ == '__main__':\n # connect to database and get all data\n get_all(connect('employee'))\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console.<\/p>\n
Logs<\/em><\/span><\/p>\n
EMPLOYEE INFORMATION\n-------------------------------------\nId = 1, Name = Marga Cronchey, Email = mcronchey0@pen.io, Gender = F, Phone = 314-289-7265\nId = 2, Name = Theda Mushrow, Email = tmushrow1@whitehouse.gov, Gender = F, Phone = 804-163-9834\nId = 3, Name = Marielle Bonicelli, Email = mbonicelli2@sitemeter.com, Gender = F, Phone = 624-922-2416\nId = 4, Name = Locke Watkinson, Email = lwatkinson3@accuweather.com, Gender = M, Phone = 456-260-1052\nId = 5, Name = Blakelee Wilcot, Email = bwilcot4@twitpic.com, Gender = M, Phone = 608-344-4090\n\nDatabase connection closed.\n<\/pre>\n
2.7 Update a record in the table<\/h3>\n
Add the following code to the python script which will update an employee record<\/span> into the table. The python script will check if the employee is present in the database. If present<\/em> the record will be updated. If not<\/em> it will print the following message –
Employee id = 5 not found<\/code> will be printed on the console.<\/p>\nupdate.py<\/em><\/span><\/p>\n
from connecttodb import *\nfrom helper import *\n# update a record\ndef update(conn, eid):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n # sql query\n query = '''UPDATE employee SET gender = %s WHERE id = %s;'''\n try:\n record = get_by_id(cursor, eid)\n if record is None:\n print('Employee id = {} not found'.format(eid))\n else:\n # execute the command\n cursor.execute(query, ['F', eid])\n # commit the changes\n conn.commit()\n print('Employee id = {} updated successfully'.format(eid))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed')\n# driver code\nif __name__ == '__main__':\n # connect to database and update a record\n update(connect('employee'), 5)\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console. You can run the
getall.py<\/code> python script to fetch the records.<\/p>\nLogs<\/em><\/span><\/p>\n
Employee id = 5 updated successfully\n\nDatabase connection closed.\n<\/pre>\n
2.8 Delete a record in the table<\/h3>\n
Add the following code to the python script which will delete an employee record<\/span> into the table. The python script will check if the employee is present in the database. If present<\/em> the record will be deleted. If not<\/em> it will print the following message –
Employee id = 5 not found<\/code> will be printed on the console.<\/p>\ndelete.py<\/em><\/span><\/p>\n
from connecttodb import *\nfrom helper import *\n# delete a record\ndef delete(conn, eid):\n # creating a cursor to perform a sql operation\n cursor = conn.cursor()\n # sql query\n query = '''DELETE FROM employee WHERE id = %s;'''\n try:\n record = get_by_id(cursor, eid)\n if record is None:\n print('Employee id = {} not found'.format(eid))\n else:\n # execute the command\n cursor.execute(query, [eid])\n # commit the changes\n conn.commit()\n print('Employee id = {} deleted successfully'.format(eid))\n except(Exception, Error) as error:\n print(error)\n finally:\n if conn is not None:\n cursor.close()\n conn.close()\n print('\\nDatabase connection closed')\n# driver code\nif __name__ == '__main__':\n # connect to database and delete a record\n delete(connect('employee'), 5)\n<\/pre>\nIf everything goes well the following output will be shown in the IDE console. You can run the
getall.py<\/code> python script to fetch the records.<\/p>\nLogs<\/em><\/span><\/p>\n
Employee id = 5 deleted successfully\n\nDatabase connection closed.\n<\/pre>\n
Similarly, we can create other scripts like
deleteall.py<\/code> to delete all records from theemployee<\/code> database and many others. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!<\/p>\n3. Summary<\/h2>\n
In this tutorial, we learned:<\/p>\n
- \n
- Introduction to psycopg2 module<\/li>\n
- Sample program to connect to the database with the help of psycopg2 module<\/li>\n
- Sample programs to perform CRUD operations<\/li>\n<\/ul>\n
Now, you can check our Python PostgreSQL Tutorial Using Psycopg2<\/a>.<\/p>\n
You can download the source code of this tutorial from the Downloads<\/a> section.<\/p>\n
<\/a>4. Download the Project<\/h2>\n
This was a python programming tutorial to connect to the PostgreSQL database with the help of the psycopg2 module and perform CRUD operations.<\/p>\n
- password<\/strong> \u2013 Credential of the identity user<\/li>\n
- user<\/strong> \u2013 Identity user to work with the PostgreSQL database. Default PostgreSQL user is