In this article, you’ll learn to connect the PostgreSQL database with Python.<\/p>\n\n\n\n
<\/a>Pre-requisite<\/h1>\n\n\n\nIn order to work with a PostgreSQL database and perform various operations on the data stored in the database, it is necessary to have a basic understanding of Structured Query Language<\/strong> (SQL). With a basic knowledge of SQL, you can create tables, insert and retrieve data, update and delete data, and perform other operations on the data stored in the database.<\/p>\n\n\n\n<\/a>PostgreSQL and Python<\/h1>\n\n\n\nPostgreSQL<\/strong> and Python<\/strong> can be used together in several ways. For example, you can use Python to connect to a PostgreSQL database and perform various operations on the data stored in the database, such as inserting, updating, or deleting data. You can also use Python to retrieve data from the database and perform analysis or other operations on the data.<\/p>\n\n\n\nTo use PostgreSQL with Python, you must install a library such as psycopg2<\/strong>, which provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\n<\/a>Psycopg2 lib<\/h1>\n\n\n\npsycopg2<\/strong> is a popular Python library for working with PostgreSQL databases. It is a PostgreSQL adapter for Python that provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\nWith psycopg2<\/strong>, you can connect to a PostgreSQL database and perform various operations on the data stored in the database, such as inserting, updating, or deleting data. You can also use psycopg2<\/strong> to retrieve data from the database and perform analysis or other operations on the data.<\/p>\n\n\n\nTo install the psycopg2<\/code> library, you will need to use a package manager like pip<\/strong>. Open a terminal or command prompt window and type the following command:<\/p>\n\n\n\npip install psycopg2<\/pre><\/div>\n\n\n\nThis will install the psycopg2<\/code> library and all of its dependencies.<\/p>\n\n\n\n<\/a>Installing pgAdmin4<\/h1>\n\n\n\npgAdmin4<\/strong> is the latest version of pgAdmin, a popular web-based graphical tool for managing and developing PostgreSQL databases. It provides a user-friendly interface that allows you to create and modify databases, tables, views, and stored procedures, run SQL queries and scripts, and manage users and permissions.<\/p>\n\n\n\nHowever, you can download and install the pgAdmin4 app for Windows and several other options<\/a> are also available for different platforms.<\/p>\n\n\n\nTo use pgAdmin4, you will need to install it on your machine and then launch it from the start menu or desktop shortcut. When the application opens, you will be presented with a login screen where you can enter your credentials to connect to a PostgreSQL server. Once you are connected to the server, you can use the pgAdmin interface to manage and develop your databases.<\/p>\n\n\n\n
<\/a>Creating databases and tables in pgAdmin4<\/h1>\n\n\n\nTo create a database and table using the pgAdmin4 app, you can follow these steps:<\/p>\n\n\n\n
\n- Launch the pgAdmin4 app and connect to a PostgreSQL server by entering your credentials in the login screen.<\/li>\n\n\n\n
- In the object tree on the left side of the pgAdmin4 window, right-click on the “Databases<\/strong>” node and then hover on the “Create<\/strong>” button and select “Database…<\/strong>“. In the “Database<\/strong>” dialog, enter a name for your database and click “Save<\/strong>“.<\/li>\n<\/ul>\n\n\n\n
![\"Creating](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/p1-1024x608.png\")
<\/figure>\n\n\n\n\n- To create a table within the database you just created, right-click on the database in the object tree and select “Query Tool<\/strong>“. This will open a new query window where you can enter SQL commands to create tables.<\/li>\n\n\n\n
- In the query window, enter the SQL command to create a table. For example, you can use the following SQL command to create a simple table called “players<\/strong>” with three columns:<\/li>\n<\/ul>\n\n\n\n
CREATE TABLE players (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n age TEXT NOT NULL\n);<\/pre><\/div>\n\n\n\n
\n- Click the “Execute<\/strong>” button in the query window to execute the SQL command and create the table.<\/li>\n<\/ul>\n\n\n\n
![\"Creating](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/p2.png\")
<\/figure>\n\n\n\n\n- To view the table you just created, expand the database<\/strong> node in the object tree, expand the “Schemas<\/strong>” node, and click on the “Tables<\/strong>” node. The table you created should be listed under the “Tables<\/strong>” node. You can double-click on the table to view its contents and structure.<\/li>\n<\/ul>\n\n\n\n
Now your table is ready and the next part is to connect<\/strong> this database with Python and manipulate the data inside the table that was just created.<\/p>\n\n\n\n<\/a>Connecting database with Python<\/h1>\n\n\n\nTo connect the PostgreSQL database with Python, you will need the psycopg2<\/strong> library installed at the beginning of the article.<\/p>\n\n\n\nOpen your favourite code editor and create a directory and then create a database.py<\/code> file inside it and write the code for connecting the database using the psycopg2<\/em><\/strong> library.<\/p>\n\n\n\nimport psycopg2 as pg\n\ntry:\n conn = pg.connect(\n host='localhost',\n database='Pg2Py',\n port=5432,\n user='postgres',\n password='*********'\n )\n\n cursor = conn.cursor()\n print(\"Connection established.\")\n\nexcept Exception as err:\n print(\"Something went wrong.\")\n print(err)<\/pre><\/div>\n\n\n\nIn the code above, psycopg2.connect()<\/code> function establishes a connection to the database using the specified configurations like host<\/em><\/strong>, database<\/em><\/strong>, port<\/em><\/strong>, user<\/em><\/strong>, and password.<\/em><\/strong> The cursor()<\/code> will allow interaction with the database.<\/p>\n\n\n\nThe above code will try to connect with the database and if it is connected successfully then it’ll prompt a message saying “Connection established<\/strong>” otherwise it’ll prompt an error message with the error.<\/p>\n\n\n\n<\/a>Fetching data from the database<\/h1>\n\n\n\nThe connection to the PostgreSQL database is now established with the help of Python and the psycopg2<\/code> library.<\/p>\n\n\n\nHere is an example of fetching the data present inside the table in the database.<\/p>\n\n\n\n
def fetch_data():\n cursor.execute('''SELECT * FROM players''')\n data = cursor.fetchall()\n return data\n\ndetails = fetch_data()\nfor row in details:\n print(row)<\/pre><\/div>\n\n\n\nIn the above code, the cursor.execute()<\/code> function executes the SQL query to select all the data from the table “players<\/strong>” and cursor.fetchall()<\/code> fetches the data and stores it inside the data<\/code> variable.<\/p>\n\n\n\nIn the next block of code, the data has been displayed by calling the fetch_data<\/code> function.<\/p>\n\n\n\n(2, 'Rohit Sharma', '34')\n(1, 'Virat Kohli', '33')<\/pre><\/div>\n\n\n\nThe above data fetched from the Pg2Py<\/code> database was added manually in the table from the pgAdmin4 app.<\/p>\n\n\n\n<\/a>Creating an entry in the database<\/h1>\n\n\n\nThe data can be added to the database using Python. The process is very simple, you just need to execute the SQL query to insert the data.<\/p>\n\n\n\n
To insert the data into the database write the following code:<\/p>\n\n\n\n
def create_entry():\n cursor.execute('''INSERT INTO players (id, name, age) \n VALUES (%s, %s, %s)''', (4, 'My Ex', 22))\n\n add_data = cursor.fetchone()\n conn.commit()\n return add_data\n\ndata = create_entry()\nprint(data)<\/pre><\/div>\n\n\n\nThe create_entry()<\/code> function contains the cursor.execute()<\/code> function that executes the SQL query to insert the data into the “players<\/strong>” table. Here, the simple SQL statement “INSERT INTO<\/code>” used will insert the values specified after the “VALUES<\/code>” statement.<\/p>\n\n\n\nThe %s<\/code> placeholders used in the above code are good practices when adding data or updating specific data. This will help you when your data is coming dynamically into your database.<\/p>\n\n\n\nThe cursor.fetchone()<\/code> function was used to get the added data and this time the commit()<\/code> function was used to commit the changes to the database<\/strong>. After committing the changes, the data was displayed.<\/p>\n\n\n\n(4, 'My Ex', '22')<\/pre><\/div>\n\n\n\nThe above code added the data into the “players<\/strong>” table and it can be seen easily in the pgAdmin4 app. Run the SQL query SELECT * FROM players;<\/code> in the query tool of the “players<\/strong>” table.<\/p>\n\n\n\n![\"Data](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/p3.png\")
<\/figure>\n\n\n\n<\/a>Deleting an entry from the database<\/h1>\n\n\n\nYou can perform a deletion action to delete the specific entry in your database. To delete the specific data, you will need to use the conditional statement in the SQL query. Suppose you want to delete the player name “Lionel Messi<\/strong>” from the database, then you will need to use the condition where you will specify that this specific name has to delete.<\/p>\n\n\n\nHere’s how you can perform the action using the code:<\/p>\n\n\n\n
def delete_entry():\n cursor.execute('''DELETE FROM players \n WHERE name = %s''', (\"Rohit Sharma\",))\n conn.commit()\n return 'Data deleted successfully'\n\ndata = delete_entry()\nprint(data)<\/pre><\/div>\n\n\n\nIn the above code, the “DELETE FROM<\/code>” statement has been used with the “WHERE<\/code>” clause that will delete the entry from the database with a name equal to the player name “Rohit Sharma<\/strong>“.<\/p>\n\n\n\nSimilarly, you can use other column<\/strong> names from the table to delete an entry from the database. Most developers prefer using the column name that contains unique values in the table.<\/p>\n\n\n\nThe value for the WHERE<\/code> clause is passed as a tuple<\/strong> to the cursor.execute()<\/code> because it accepts only tuple value. If you will try to pass a value other than the tuple it’ll throw you an error.<\/p>\n\n\n\nFinally, the changes were committed to the database using the conn.commit()<\/code> method.<\/p>\n\n\n\nData deleted successfully<\/pre><\/div>\n\n\n\nYou can see the entry in the database has been deleted.<\/p>\n\n\n\n![\"Data](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/p4.png\")
<\/figure>\n\n\n\n<\/a>Updating an entry<\/h1>\n\n\n\nThe most integral part of any application is to update its existing data and of course, that updated data has to be reflected in the database. As usual, you need to run the SQL query for updating the specific entry.<\/p>\n\n\n\n
Here’s the Python code for updating the entry in the database.<\/p>\n\n\n\n
def update_entry():\n cursor.execute('''UPDATE players\n SET name = %s, age = %s WHERE id = %s''',\n ('My other ex', 21, 2))\n conn.commit()\n return 'Updated successfully'\n\ndata = update_entry()\nprint(data)<\/pre><\/div>\n\n\n\nIn the above code, a SQL query is executed using the execute()<\/code> method in which the “UPDATE<\/code>” statement is used to update the “players<\/strong>” table and the “SET<\/code>” statement is used to set the updated values to the existing data. The “WHERE<\/code>” clause is used to specify the ID<\/strong> of the data to be updated.<\/p>\n\n\n\nAnd then the changes were committed to the database using the commit()<\/code> method.<\/p>\n\n\n\n![\"Updated](\"https:\/\/geekpython.in\/wp-content\/uploads\/2023\/08\/p5.png\")
<\/figure>\n\n\n\nThe existing data with an ID<\/strong> of 2<\/strong> is now updated with the specified data in the Python code and is successfully reflected in the database. You might be thinking that there was no data with an ID equal to 2 before. Data with an ID equal to 2 was manually added before updating the existing data.<\/p>\n\n\n\n<\/a>Conclusion<\/h1>\n\n\n\nThe article focuses on connecting the PostgreSQL database with Python using the PostgreSQL database adapter library called psycopg2<\/code>. However, the complete process was not executed using Python code. Fetching, creating, deleting, and inserting operations were executed with the help of SQL queries in Python.<\/p>\n\n\n\nThere are popular ORMs (Object Relation Mapper) available that make the complete operations done using only Python code.<\/p>\n\n\n\n
But this article has a mix of both Python and SQL to integrate the PostgreSQL database with Python to perform operations like updating, creating, inserting, and much more into the database by running the Python code.<\/p>\n\n\n\n
Let’s recall what you’ve learned:<\/p>\n\n\n\n
\n- Connecting the PostgreSQL database with Python<\/strong><\/li>\n\n\n\n
- Fetching the data from the database<\/strong><\/li>\n\n\n\n
- Creating the entry into the database<\/strong><\/li>\n\n\n\n
- Deleting the entry from the database<\/strong><\/li>\n\n\n\n
- Updating the existing entry in the database<\/strong><\/li>\n<\/ul>\n\n\n\n
This will help you create a connection between the database and Python.<\/p>\n\n\n\n
\n\n\n\n\ud83c\udfc6Other articles you might like<\/strong><\/p>\n\n\n\n\u2705Create a dynamic contact form having a database and Gmail connected with it<\/a>.<\/p>\n\n\n\n\u2705Build a custom deep learning model using transfer learning<\/a>.<\/p>\n\n\n\n\u2705Display dynamic and static images on the frontend using Flask<\/a>.<\/p>\n\n\n\n\u2705Build your first command line interface using Python<\/a>.<\/p>\n\n\n\n\u2705NumPy and Tensorflow have a common function<\/a>.<\/p>\n\n\n\n
<\/a>PostgreSQL and Python<\/h1>\n\n\n\nPostgreSQL<\/strong> and Python<\/strong> can be used together in several ways. For example, you can use Python to connect to a PostgreSQL database and perform various operations on the data stored in the database, such as inserting, updating, or deleting data. You can also use Python to retrieve data from the database and perform analysis or other operations on the data.<\/p>\n\n\n\nTo use PostgreSQL with Python, you must install a library such as psycopg2<\/strong>, which provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\n<\/a>Psycopg2 lib<\/h1>\n\n\n\npsycopg2<\/strong> is a popular Python library for working with PostgreSQL databases. It is a PostgreSQL adapter for Python that provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\nWith psycopg2<\/strong>, you can connect to a PostgreSQL database and perform various operations on the data stored in the database, such as inserting, updating, or deleting data. You can also use psycopg2<\/strong> to retrieve data from the database and perform analysis or other operations on the data.<\/p>\n\n\n\nTo install the psycopg2<\/code> library, you will need to use a package manager like pip<\/strong>. Open a terminal or command prompt window and type the following command:<\/p>\n\n\n\npip install psycopg2<\/pre><\/div>\n\n\n\nThis will install the psycopg2<\/code> library and all of its dependencies.<\/p>\n\n\n\n<\/a>Installing pgAdmin4<\/h1>\n\n\n\npgAdmin4<\/strong> is the latest version of pgAdmin, a popular web-based graphical tool for managing and developing PostgreSQL databases. It provides a user-friendly interface that allows you to create and modify databases, tables, views, and stored procedures, run SQL queries and scripts, and manage users and permissions.<\/p>\n\n\n\nHowever, you can download and install the pgAdmin4 app for Windows and several other options<\/a> are also available for different platforms.<\/p>\n\n\n\nTo use pgAdmin4, you will need to install it on your machine and then launch it from the start menu or desktop shortcut. When the application opens, you will be presented with a login screen where you can enter your credentials to connect to a PostgreSQL server. Once you are connected to the server, you can use the pgAdmin interface to manage and develop your databases.<\/p>\n\n\n\n
<\/a>Creating databases and tables in pgAdmin4<\/h1>\n\n\n\nTo create a database and table using the pgAdmin4 app, you can follow these steps:<\/p>\n\n\n\n
\n- Launch the pgAdmin4 app and connect to a PostgreSQL server by entering your credentials in the login screen.<\/li>\n\n\n\n
- In the object tree on the left side of the pgAdmin4 window, right-click on the “Databases<\/strong>” node and then hover on the “Create<\/strong>” button and select “Database…<\/strong>“. In the “Database<\/strong>” dialog, enter a name for your database and click “Save<\/strong>“.<\/li>\n<\/ul>\n\n\n\n
<\/figure>\n\n\n\n\n- To create a table within the database you just created, right-click on the database in the object tree and select “Query Tool<\/strong>“. This will open a new query window where you can enter SQL commands to create tables.<\/li>\n\n\n\n
- In the query window, enter the SQL command to create a table. For example, you can use the following SQL command to create a simple table called “players<\/strong>” with three columns:<\/li>\n<\/ul>\n\n\n\n
CREATE TABLE players (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n age TEXT NOT NULL\n);<\/pre><\/div>\n\n\n\n
\n- Click the “Execute<\/strong>” button in the query window to execute the SQL command and create the table.<\/li>\n<\/ul>\n\n\n\n
<\/figure>\n\n\n\n\n- To view the table you just created, expand the database<\/strong> node in the object tree, expand the “Schemas<\/strong>” node, and click on the “Tables<\/strong>” node. The table you created should be listed under the “Tables<\/strong>” node. You can double-click on the table to view its contents and structure.<\/li>\n<\/ul>\n\n\n\n
Now your table is ready and the next part is to connect<\/strong> this database with Python and manipulate the data inside the table that was just created.<\/p>\n\n\n\n<\/a>Connecting database with Python<\/h1>\n\n\n\nTo connect the PostgreSQL database with Python, you will need the psycopg2<\/strong> library installed at the beginning of the article.<\/p>\n\n\n\nOpen your favourite code editor and create a directory and then create a database.py<\/code> file inside it and write the code for connecting the database using the psycopg2<\/em><\/strong> library.<\/p>\n\n\n\nimport psycopg2 as pg\n\ntry:\n conn = pg.connect(\n host='localhost',\n database='Pg2Py',\n port=5432,\n user='postgres',\n password='*********'\n )\n\n cursor = conn.cursor()\n print(\"Connection established.\")\n\nexcept Exception as err:\n print(\"Something went wrong.\")\n print(err)<\/pre><\/div>\n\n\n\nIn the code above, psycopg2.connect()<\/code> function establishes a connection to the database using the specified configurations like host<\/em><\/strong>, database<\/em><\/strong>, port<\/em><\/strong>, user<\/em><\/strong>, and password.<\/em><\/strong> The cursor()<\/code> will allow interaction with the database.<\/p>\n\n\n\nThe above code will try to connect with the database and if it is connected successfully then it’ll prompt a message saying “Connection established<\/strong>” otherwise it’ll prompt an error message with the error.<\/p>\n\n\n\n<\/a>Fetching data from the database<\/h1>\n\n\n\nThe connection to the PostgreSQL database is now established with the help of Python and the psycopg2<\/code> library.<\/p>\n\n\n\nHere is an example of fetching the data present inside the table in the database.<\/p>\n\n\n\n
def fetch_data():\n cursor.execute('''SELECT * FROM players''')\n data = cursor.fetchall()\n return data\n\ndetails = fetch_data()\nfor row in details:\n print(row)<\/pre><\/div>\n\n\n\nIn the above code, the cursor.execute()<\/code> function executes the SQL query to select all the data from the table “players<\/strong>” and cursor.fetchall()<\/code> fetches the data and stores it inside the data<\/code> variable.<\/p>\n\n\n\nIn the next block of code, the data has been displayed by calling the fetch_data<\/code> function.<\/p>\n\n\n\n(2, 'Rohit Sharma', '34')\n(1, 'Virat Kohli', '33')<\/pre><\/div>\n\n\n\nThe above data fetched from the Pg2Py<\/code> database was added manually in the table from the pgAdmin4 app.<\/p>\n\n\n\n<\/a>Creating an entry in the database<\/h1>\n\n\n\nThe data can be added to the database using Python. The process is very simple, you just need to execute the SQL query to insert the data.<\/p>\n\n\n\n
To insert the data into the database write the following code:<\/p>\n\n\n\n
def create_entry():\n cursor.execute('''INSERT INTO players (id, name, age) \n VALUES (%s, %s, %s)''', (4, 'My Ex', 22))\n\n add_data = cursor.fetchone()\n conn.commit()\n return add_data\n\ndata = create_entry()\nprint(data)<\/pre><\/div>\n\n\n\nThe create_entry()<\/code> function contains the cursor.execute()<\/code> function that executes the SQL query to insert the data into the “players<\/strong>” table. Here, the simple SQL statement “INSERT INTO<\/code>” used will insert the values specified after the “VALUES<\/code>” statement.<\/p>\n\n\n\nThe %s<\/code> placeholders used in the above code are good practices when adding data or updating specific data. This will help you when your data is coming dynamically into your database.<\/p>\n\n\n\nThe cursor.fetchone()<\/code> function was used to get the added data and this time the commit()<\/code> function was used to commit the changes to the database<\/strong>. After committing the changes, the data was displayed.<\/p>\n\n\n\n(4, 'My Ex', '22')<\/pre><\/div>\n\n\n\nThe above code added the data into the “players<\/strong>” table and it can be seen easily in the pgAdmin4 app. Run the SQL query SELECT * FROM players;<\/code> in the query tool of the “players<\/strong>” table.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/a>Deleting an entry from the database<\/h1>\n\n\n\nYou can perform a deletion action to delete the specific entry in your database. To delete the specific data, you will need to use the conditional statement in the SQL query. Suppose you want to delete the player name “Lionel Messi<\/strong>” from the database, then you will need to use the condition where you will specify that this specific name has to delete.<\/p>\n\n\n\nHere’s how you can perform the action using the code:<\/p>\n\n\n\n
def delete_entry():\n cursor.execute('''DELETE FROM players \n WHERE name = %s''', (\"Rohit Sharma\",))\n conn.commit()\n return 'Data deleted successfully'\n\ndata = delete_entry()\nprint(data)<\/pre><\/div>\n\n\n\nIn the above code, the “DELETE FROM<\/code>” statement has been used with the “WHERE<\/code>” clause that will delete the entry from the database with a name equal to the player name “Rohit Sharma<\/strong>“.<\/p>\n\n\n\nSimilarly, you can use other column<\/strong> names from the table to delete an entry from the database. Most developers prefer using the column name that contains unique values in the table.<\/p>\n\n\n\nThe value for the WHERE<\/code> clause is passed as a tuple<\/strong> to the cursor.execute()<\/code> because it accepts only tuple value. If you will try to pass a value other than the tuple it’ll throw you an error.<\/p>\n\n\n\nFinally, the changes were committed to the database using the conn.commit()<\/code> method.<\/p>\n\n\n\nData deleted successfully<\/pre><\/div>\n\n\n\nYou can see the entry in the database has been deleted.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/a>Updating an entry<\/h1>\n\n\n\nThe most integral part of any application is to update its existing data and of course, that updated data has to be reflected in the database. As usual, you need to run the SQL query for updating the specific entry.<\/p>\n\n\n\n
Here’s the Python code for updating the entry in the database.<\/p>\n\n\n\n
def update_entry():\n cursor.execute('''UPDATE players\n SET name = %s, age = %s WHERE id = %s''',\n ('My other ex', 21, 2))\n conn.commit()\n return 'Updated successfully'\n\ndata = update_entry()\nprint(data)<\/pre><\/div>\n\n\n\nIn the above code, a SQL query is executed using the execute()<\/code> method in which the “UPDATE<\/code>” statement is used to update the “players<\/strong>” table and the “SET<\/code>” statement is used to set the updated values to the existing data. The “WHERE<\/code>” clause is used to specify the ID<\/strong> of the data to be updated.<\/p>\n\n\n\nAnd then the changes were committed to the database using the commit()<\/code> method.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe existing data with an ID<\/strong> of 2<\/strong> is now updated with the specified data in the Python code and is successfully reflected in the database. You might be thinking that there was no data with an ID equal to 2 before. Data with an ID equal to 2 was manually added before updating the existing data.<\/p>\n\n\n\n<\/a>Conclusion<\/h1>\n\n\n\nThe article focuses on connecting the PostgreSQL database with Python using the PostgreSQL database adapter library called psycopg2<\/code>. However, the complete process was not executed using Python code. Fetching, creating, deleting, and inserting operations were executed with the help of SQL queries in Python.<\/p>\n\n\n\nThere are popular ORMs (Object Relation Mapper) available that make the complete operations done using only Python code.<\/p>\n\n\n\n
But this article has a mix of both Python and SQL to integrate the PostgreSQL database with Python to perform operations like updating, creating, inserting, and much more into the database by running the Python code.<\/p>\n\n\n\n
Let’s recall what you’ve learned:<\/p>\n\n\n\n
\n- Connecting the PostgreSQL database with Python<\/strong><\/li>\n\n\n\n
- Fetching the data from the database<\/strong><\/li>\n\n\n\n
- Creating the entry into the database<\/strong><\/li>\n\n\n\n
- Deleting the entry from the database<\/strong><\/li>\n\n\n\n
- Updating the existing entry in the database<\/strong><\/li>\n<\/ul>\n\n\n\n
This will help you create a connection between the database and Python.<\/p>\n\n\n\n
\n\n\n\n\ud83c\udfc6Other articles you might like<\/strong><\/p>\n\n\n\n\u2705Create a dynamic contact form having a database and Gmail connected with it<\/a>.<\/p>\n\n\n\n\u2705Build a custom deep learning model using transfer learning<\/a>.<\/p>\n\n\n\n\u2705Display dynamic and static images on the frontend using Flask<\/a>.<\/p>\n\n\n\n\u2705Build your first command line interface using Python<\/a>.<\/p>\n\n\n\n\u2705NumPy and Tensorflow have a common function<\/a>.<\/p>\n\n\n\n
To use PostgreSQL with Python, you must install a library such as psycopg2<\/strong>, which provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\n psycopg2<\/strong> is a popular Python library for working with PostgreSQL databases. It is a PostgreSQL adapter for Python that provides a Python interface to the PostgreSQL database.<\/p>\n\n\n\n With psycopg2<\/strong>, you can connect to a PostgreSQL database and perform various operations on the data stored in the database, such as inserting, updating, or deleting data. You can also use psycopg2<\/strong> to retrieve data from the database and perform analysis or other operations on the data.<\/p>\n\n\n\n To install the This will install the pgAdmin4<\/strong> is the latest version of pgAdmin, a popular web-based graphical tool for managing and developing PostgreSQL databases. It provides a user-friendly interface that allows you to create and modify databases, tables, views, and stored procedures, run SQL queries and scripts, and manage users and permissions.<\/p>\n\n\n\n However, you can download and install the pgAdmin4 app for Windows and several other options<\/a> are also available for different platforms.<\/p>\n\n\n\n To use pgAdmin4, you will need to install it on your machine and then launch it from the start menu or desktop shortcut. When the application opens, you will be presented with a login screen where you can enter your credentials to connect to a PostgreSQL server. Once you are connected to the server, you can use the pgAdmin interface to manage and develop your databases.<\/p>\n\n\n\n To create a database and table using the pgAdmin4 app, you can follow these steps:<\/p>\n\n\n\n Now your table is ready and the next part is to connect<\/strong> this database with Python and manipulate the data inside the table that was just created.<\/p>\n\n\n\n To connect the PostgreSQL database with Python, you will need the psycopg2<\/strong> library installed at the beginning of the article.<\/p>\n\n\n\n Open your favourite code editor and create a directory and then create a In the code above, The above code will try to connect with the database and if it is connected successfully then it’ll prompt a message saying “Connection established<\/strong>” otherwise it’ll prompt an error message with the error.<\/p>\n\n\n\n The connection to the PostgreSQL database is now established with the help of Python and the Here is an example of fetching the data present inside the table in the database.<\/p>\n\n\n\n In the above code, the In the next block of code, the data has been displayed by calling the The above data fetched from the The data can be added to the database using Python. The process is very simple, you just need to execute the SQL query to insert the data.<\/p>\n\n\n\n To insert the data into the database write the following code:<\/p>\n\n\n\n The The The The above code added the data into the “players<\/strong>” table and it can be seen easily in the pgAdmin4 app. Run the SQL query You can perform a deletion action to delete the specific entry in your database. To delete the specific data, you will need to use the conditional statement in the SQL query. Suppose you want to delete the player name “Lionel Messi<\/strong>” from the database, then you will need to use the condition where you will specify that this specific name has to delete.<\/p>\n\n\n\n Here’s how you can perform the action using the code:<\/p>\n\n\n\n In the above code, the “ Similarly, you can use other column<\/strong> names from the table to delete an entry from the database. Most developers prefer using the column name that contains unique values in the table.<\/p>\n\n\n\n The value for the Finally, the changes were committed to the database using the You can see the entry in the database has been deleted.<\/p>\n\n\n\n The most integral part of any application is to update its existing data and of course, that updated data has to be reflected in the database. As usual, you need to run the SQL query for updating the specific entry.<\/p>\n\n\n\n Here’s the Python code for updating the entry in the database.<\/p>\n\n\n\n In the above code, a SQL query is executed using the And then the changes were committed to the database using the The existing data with an ID<\/strong> of 2<\/strong> is now updated with the specified data in the Python code and is successfully reflected in the database. You might be thinking that there was no data with an ID equal to 2 before. Data with an ID equal to 2 was manually added before updating the existing data.<\/p>\n\n\n\n The article focuses on connecting the PostgreSQL database with Python using the PostgreSQL database adapter library called There are popular ORMs (Object Relation Mapper) available that make the complete operations done using only Python code.<\/p>\n\n\n\n But this article has a mix of both Python and SQL to integrate the PostgreSQL database with Python to perform operations like updating, creating, inserting, and much more into the database by running the Python code.<\/p>\n\n\n\n Let’s recall what you’ve learned:<\/p>\n\n\n\n This will help you create a connection between the database and Python.<\/p>\n\n\n\n \ud83c\udfc6Other articles you might like<\/strong><\/p>\n\n\n\n \u2705Create a dynamic contact form having a database and Gmail connected with it<\/a>.<\/p>\n\n\n\n \u2705Build a custom deep learning model using transfer learning<\/a>.<\/p>\n\n\n\n \u2705Display dynamic and static images on the frontend using Flask<\/a>.<\/p>\n\n\n\n \u2705Build your first command line interface using Python<\/a>.<\/p>\n\n\n\n \u2705NumPy and Tensorflow have a common function<\/a>.<\/p>\n\n\n\n<\/a>Psycopg2 lib<\/h1>\n\n\n\n
psycopg2<\/code> library, you will need to use a package manager like pip<\/strong>. Open a terminal or command prompt window and type the following command:<\/p>\n\n\n\npip install psycopg2<\/pre><\/div>\n\n\n\n
psycopg2<\/code> library and all of its dependencies.<\/p>\n\n\n\n<\/a>Installing pgAdmin4<\/h1>\n\n\n\n
<\/a>Creating databases and tables in pgAdmin4<\/h1>\n\n\n\n
\n
<\/figure>\n\n\n\n\n
CREATE TABLE players (\n id INTEGER PRIMARY KEY,\n name TEXT NOT NULL,\n age TEXT NOT NULL\n);<\/pre><\/div>\n\n\n\n
\n
<\/figure>\n\n\n\n\n
<\/a>Connecting database with Python<\/h1>\n\n\n\n
database.py<\/code> file inside it and write the code for connecting the database using the psycopg2<\/em><\/strong> library.<\/p>\n\n\n\nimport psycopg2 as pg\n\ntry:\n conn = pg.connect(\n host='localhost',\n database='Pg2Py',\n port=5432,\n user='postgres',\n password='*********'\n )\n\n cursor = conn.cursor()\n print(\"Connection established.\")\n\nexcept Exception as err:\n print(\"Something went wrong.\")\n print(err)<\/pre><\/div>\n\n\n\n
psycopg2.connect()<\/code> function establishes a connection to the database using the specified configurations like host<\/em><\/strong>, database<\/em><\/strong>, port<\/em><\/strong>, user<\/em><\/strong>, and password.<\/em><\/strong> The cursor()<\/code> will allow interaction with the database.<\/p>\n\n\n\n<\/a>Fetching data from the database<\/h1>\n\n\n\n
psycopg2<\/code> library.<\/p>\n\n\n\ndef fetch_data():\n cursor.execute('''SELECT * FROM players''')\n data = cursor.fetchall()\n return data\n\ndetails = fetch_data()\nfor row in details:\n print(row)<\/pre><\/div>\n\n\n\ncursor.execute()<\/code> function executes the SQL query to select all the data from the table “players<\/strong>” and cursor.fetchall()<\/code> fetches the data and stores it inside the data<\/code> variable.<\/p>\n\n\n\nfetch_data<\/code> function.<\/p>\n\n\n\n(2, 'Rohit Sharma', '34')\n(1, 'Virat Kohli', '33')<\/pre><\/div>\n\n\n\n
Pg2Py<\/code> database was added manually in the table from the pgAdmin4 app.<\/p>\n\n\n\n<\/a>Creating an entry in the database<\/h1>\n\n\n\n
def create_entry():\n cursor.execute('''INSERT INTO players (id, name, age) \n VALUES (%s, %s, %s)''', (4, 'My Ex', 22))\n\n add_data = cursor.fetchone()\n conn.commit()\n return add_data\n\ndata = create_entry()\nprint(data)<\/pre><\/div>\n\n\n\ncreate_entry()<\/code> function contains the cursor.execute()<\/code> function that executes the SQL query to insert the data into the “players<\/strong>” table. Here, the simple SQL statement “INSERT INTO<\/code>” used will insert the values specified after the “VALUES<\/code>” statement.<\/p>\n\n\n\n%s<\/code> placeholders used in the above code are good practices when adding data or updating specific data. This will help you when your data is coming dynamically into your database.<\/p>\n\n\n\ncursor.fetchone()<\/code> function was used to get the added data and this time the commit()<\/code> function was used to commit the changes to the database<\/strong>. After committing the changes, the data was displayed.<\/p>\n\n\n\n(4, 'My Ex', '22')<\/pre><\/div>\n\n\n\n
SELECT * FROM players;<\/code> in the query tool of the “players<\/strong>” table.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/a>Deleting an entry from the database<\/h1>\n\n\n\n
def delete_entry():\n cursor.execute('''DELETE FROM players \n WHERE name = %s''', (\"Rohit Sharma\",))\n conn.commit()\n return 'Data deleted successfully'\n\ndata = delete_entry()\nprint(data)<\/pre><\/div>\n\n\n\nDELETE FROM<\/code>” statement has been used with the “WHERE<\/code>” clause that will delete the entry from the database with a name equal to the player name “Rohit Sharma<\/strong>“.<\/p>\n\n\n\nWHERE<\/code> clause is passed as a tuple<\/strong> to the cursor.execute()<\/code> because it accepts only tuple value. If you will try to pass a value other than the tuple it’ll throw you an error.<\/p>\n\n\n\nconn.commit()<\/code> method.<\/p>\n\n\n\nData deleted successfully<\/pre><\/div>\n\n\n\n
<\/figure>\n\n\n\n<\/a>Updating an entry<\/h1>\n\n\n\n
def update_entry():\n cursor.execute('''UPDATE players\n SET name = %s, age = %s WHERE id = %s''',\n ('My other ex', 21, 2))\n conn.commit()\n return 'Updated successfully'\n\ndata = update_entry()\nprint(data)<\/pre><\/div>\n\n\n\nexecute()<\/code> method in which the “UPDATE<\/code>” statement is used to update the “players<\/strong>” table and the “SET<\/code>” statement is used to set the updated values to the existing data. The “WHERE<\/code>” clause is used to specify the ID<\/strong> of the data to be updated.<\/p>\n\n\n\ncommit()<\/code> method.<\/p>\n\n\n\n<\/figure>\n\n\n\n<\/a>Conclusion<\/h1>\n\n\n\n
psycopg2<\/code>. However, the complete process was not executed using Python code. Fetching, creating, deleting, and inserting operations were executed with the help of SQL queries in Python.<\/p>\n\n\n\n\n
\n\n\n\n