{"id":467,"date":"2018-08-09T12:50:48","date_gmt":"2018-08-09T07:20:48","guid":{"rendered":"https:\/\/pynative.com\/?p=467"},"modified":"2021-03-09T10:48:04","modified_gmt":"2021-03-09T05:18:04","slug":"python-postgresql-tutorial","status":"publish","type":"post","link":"https:\/\/pynative.com\/python-postgresql-tutorial\/","title":{"rendered":"Python PostgreSQL Tutorial Using Psycopg2"},"content":{"rendered":"\n<p>This Python PostgreSQL tutorial demonstrates how to use the <strong>Psycopg2 module to connect to PostgreSQL<\/strong> and perform SQL queries, database operations. There are many ways we can connect to a PostgreSQL database from Python, and in this tutorial, we&#8217;re going to explore several options to see how to achieve this.<\/p>\n\n\n\n<p>Below is the list of available Python modules to work with the PostgreSQL database server.<\/p>\n\n\n\n<ul><li><strong><code>Psycopg2<\/code><\/strong><\/li><li><code>pg8000<\/code><\/li><li><code>py-postgresql<\/code><\/li><li><code>PyGreSQL<\/code><\/li><li><code>ocpgdb<\/code><\/li><li><code>bpgsql<\/code><\/li><li><code>SQLAlchemy<\/code>. SQLAlchemy needs any of the above to be installed separately.<\/li><\/ul>\n\n\n\n<p><strong>Note<em>:&nbsp;<\/em><\/strong>Above all modules adhere to Python Database API Specification v2.0 (PEP 249). This API is designed to encourage and maintain the similarity between the Python database modules to access databases. In other words, the syntax, method, and way of access the database are the same in all the above modules.<\/p>\n\n\n\n<p><strong>We stick to the Psycopg2<\/strong> because it is arguably the most popular and stable module to work with PostgreSQL. Also, We are using Psycopg2 to work with PostgreSQL because of the following reasons.<\/p>\n\n\n\n<ul><li>It is used in most of the Python and Postgres frameworks.<\/li><li>It is also <strong>actively maintained<\/strong> and supports Python&#8217;s primary version, i.e., Python 3 and Python 2.<\/li><li>It is <strong>thread-safe<\/strong> and designed for heavily multi-threaded applications. Note, threads can share the connections.<\/li><\/ul>\n\n\n\n<p><strong>This Python PostgreSQL guide mainly focuses on the followings<\/strong><\/p>\n\n\n\n<ul><li>Installing Psycopg2 and use its API to access the PostgreSQL database<\/li><li>Perform data insertion, data retrieval, data update, and data deletion through Python application.<\/li><li>Next, it will cover PostgreSQL transaction management, connection pooling, and error-handling techniques to develop robust Python programs with PostgreSQL.<\/li><\/ul>\n\n\n\n<p>Let\u2019s dive right in.<\/p>\n\n\n\n<div class=\"wp-block-yoast-seo-table-of-contents yoast-table-of-contents\"><h2>Table of contents<\/h2><ul><li><a href=\"#h-install-psycopg2-using-the-pip-command\" data-level=\"2\">Install Psycopg2 using the pip command<\/a><ul><li><a href=\"#h-verify-psycopg2-installation\" data-level=\"3\">Verify&nbsp;Psycopg2 installation<\/a><\/li><\/ul><\/li><li><a href=\"#h-python-postgresql-database-connection\" data-level=\"2\">Python PostgreSQL database connection<\/a><ul><li><a href=\"#h-python-example-to-connect-postgresql-database\" data-level=\"3\">Python example to connect PostgreSQL database<\/a><\/li><li><a href=\"#h-important-points\" data-level=\"3\">Important points <\/a><\/li><\/ul><\/li><li><a href=\"#h-create-a-postgresql-table-from-python\" data-level=\"2\">Create a PostgreSQL table from Python<\/a><\/li><li><a href=\"#h-the-mapping-between-python-and-postgresql-types\" data-level=\"2\">The mapping between Python and PostgreSQL types<\/a><ul><li><a href=\"#h-constants-and-numeric-conversion\" data-level=\"3\">Constants and numeric conversion<\/a><\/li><\/ul><\/li><li><a href=\"#h-perform-postgresql-crud-operations-from-python\" data-level=\"2\">Perform PostgreSQL CRUD operations from Python<\/a><\/li><li><a href=\"#h-working-with-postgresql-date-and-time-in-python\" data-level=\"2\">Working with PostgreSQL date and time in Python<\/a><\/li><li><a href=\"#h-call-postgresql-function-and-stored-procedure-from-python\" data-level=\"2\">Call PostgreSQL Function and Stored Procedure from Python<\/a><\/li><li><a href=\"#h-python-postgresql-transaction-management\" data-level=\"2\">Python PostgreSQL Transaction management<\/a><\/li><li><a href=\"#h-python-postgresql-connection-pooling\" data-level=\"2\">Python PostgreSQL Connection Pooling<\/a><\/li><li><a href=\"#h-python-postgresql-exercise-project\" data-level=\"2\">Python PostgreSQL Exercise Project<\/a><\/li><\/ul><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-install-psycopg2-using-the-pip-command\">Install Psycopg2 using the pip command<\/h2>\n\n\n\n<p>You need to install the current version of Psycopg2 (<strong>2.8.6<\/strong>) on your machine to use PostgreSQL from Python. This module is available on pypi.org.<\/p>\n\n\n\n<p>Using the following pip command, you can&nbsp;install Psycopg2 on any operating system, including&nbsp;Windows,&nbsp;macOS,&nbsp;Linux, Unix, and&nbsp;Ubuntu.<\/p>\n\n\n<pre id=\"syntax\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\">pip install psycopg2<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>You can also install a specific version using the following command.<\/p>\n\n\n<pre id=\"syntax\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\">pip install psycopg2=<span class=\"hljs-number\">2.8<\/span><span class=\"hljs-number\">.6<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><strong>If you are facing&nbsp;pip install error<\/strong> like \u201cconnection error:&nbsp; [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:598)\u201d. You can resolve this error by setting pypi.org and files.pythonhosted.org as trusted hosts. If you are facing a pip install error Please try following the command.<\/p>\n\n\n<div class=\"hljstoolbar\"><pre id=\"code1\"  id=\"norun\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\">python -m pip install --trusted-host pypi.org --trusted-host files.pythonhosted.org --trusted-host pypi.python.org psycopg2<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><button class=\"hljs-copy-button\" onclick=\"copy_code('code1', this);\"><i class=\"far fa-copy1\" aria-hidden=\"true\"><\/i><\/button><\/div>\n\n\n<p>The current&nbsp;psycopg2&nbsp;module supports:<\/p>\n\n\n\n<ul><li>Python version 2.7, and Python 3 versions from 3.4 to 3.8<\/li><li>PostgreSQL server versions from 7.4 to 12<\/li><li>PostgreSQL client library version from 9.1<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-verify-psycopg2-installation\">Verify&nbsp;Psycopg2 installation<\/h3>\n\n\n\n<p>You should get the following messages after running the above command.<\/p>\n\n\n\n<ul><li>Collecting psycopg2<\/li><li>Downloading psycopg2-2.8.6<\/li><li>Installing collected packages: psycopg2<\/li><li>Successfully installed psycopg2-2.8.6<\/li><\/ul>\n\n\n\n<p>Please use the following command to install Psycopg2 If you are using <strong>anaconda<\/strong>.<\/p>\n\n\n<pre id=\"syntax\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\">conda install -c anaconda psycopg2<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-python-postgresql-database-connection\">Python PostgreSQL database connection<\/h2>\n\n\n\n<p>In this section, we will learn how to connect to PostgreSQL through Python using Psycopg2.<\/p>\n\n\n\n<p><strong>Arguments required to connect PostgreSQL database from Python<\/strong><\/p>\n\n\n\n<p>You need to know the following detail of the PostgreSQL server to perform the connection.<\/p>\n\n\n\n<ul><li><strong>Username<\/strong>: The username you use to work with PostgreSQL, The default username for the PostgreSQL database is <strong>Postgres<\/strong>.<\/li><li><strong>Password<\/strong>: Password is given by the user at the time of installing the PostgreSQL.<\/li><li><strong>Host Name<\/strong>: This is&nbsp;the server name or Ip address on which PostgreSQL is running. if you are running on localhost, then you can use <strong>localhost<\/strong>, or its IP, i.e., 127.0.0.0<\/li><li><strong>Database Name<\/strong>: Database name to which you want to connect. Here we are using Database named &#8220;<strong>postgres_db<\/strong>&#8220;.<\/li><\/ul>\n\n\n\n<div class=\"schema-how-to wp-block-yoast-how-to-block\"><p class=\"schema-how-to-description\">How to Connect to PostgreSQL in Python<\/p> <ol class=\"schema-how-to-steps\"><li class=\"schema-how-to-step\" id=\"how-to-step-1612938393338\"><strong class=\"schema-how-to-step-name\">Install Psycopg2 module<\/strong> <p class=\"schema-how-to-step-text\">Install and import psycopg2 module. Import using a <code>import psycopg2<\/code> statement so you can use this module&#8217;s methods to communicate with the PostgreSQL database.<\/p> <\/li><li class=\"schema-how-to-step\" id=\"how-to-step-1612938566138\"><strong class=\"schema-how-to-step-name\">Use the connect() method <\/strong> <p class=\"schema-how-to-step-text\">Use the <code>psycopg2.connect()<\/code> method with the required arguments to connect MySQL. It would return an <code>Connection<\/code> object if the connection established successfully<\/p> <\/li><li class=\"schema-how-to-step\" id=\"how-to-step-1612939866983\"><strong class=\"schema-how-to-step-name\">Use the cursor() method<\/strong> <p class=\"schema-how-to-step-text\">Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python.<\/p> <\/li><li class=\"schema-how-to-step\" id=\"how-to-step-1612938641024\"><strong class=\"schema-how-to-step-name\">Use the execute() method<\/strong> <p class=\"schema-how-to-step-text\">The <code>execute()<\/code> methods run the SQL query and return the result.<\/p> <\/li><li class=\"schema-how-to-step\" id=\"how-to-step-1612938710846\"><strong class=\"schema-how-to-step-name\">Extract result using fetchall()<\/strong> <p class=\"schema-how-to-step-text\">Use <code>cursor.fetchall()<\/code> or <code>fetchone()<\/code> or <code>fetchmany()<\/code> to read query result.<\/p> <\/li><li class=\"schema-how-to-step\" id=\"how-to-step-1612938784560\"><strong class=\"schema-how-to-step-name\">Close cursor and connection objects<\/strong> <p class=\"schema-how-to-step-text\">use <code>cursor.clsoe()<\/code> and <code>connection.clsoe()<\/code> method to close PostgreSQL connections after your work completes<\/p> <\/li><\/ol><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"590\" height=\"358\" src=\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql_connection_to_access_database.png\" alt=\"Python PostgreSQL connection to access database\" class=\"wp-image-523\" srcset=\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql_connection_to_access_database.png 590w, https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql_connection_to_access_database-300x182.png 300w\" sizes=\"(max-width: 590px) 100vw, 590px\" \/><figcaption>Python PostgreSQL connection to access database<\/figcaption><\/figure><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-python-example-to-connect-postgresql-database\">Python example to connect PostgreSQL database<\/h3>\n\n\n\n<p>To connect the PostgreSQL database and perform SQL queries, you must know the database name you want to connect to, and if you have not created any database, I advise you to create one before proceeding further.<\/p>\n\n\n<div class=\"hljstoolbar\"><pre id=\"code2\"  id=\"norun\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\"><span class=\"hljs-keyword\">import<\/span> psycopg2\n<span class=\"hljs-keyword\">from<\/span> psycopg2 <span class=\"hljs-keyword\">import<\/span> Error\n\n<span class=\"hljs-keyword\">try<\/span>:\n    <span class=\"hljs-comment\"># Connect to an existing database<\/span>\n    connection = psycopg2.connect(user=<span class=\"hljs-string\">\"postgres\"<\/span>,\n                                  password=<span class=\"hljs-string\">\"pynative@#29\"<\/span>,\n                                  host=<span class=\"hljs-string\">\"127.0.0.1\"<\/span>,\n                                  port=<span class=\"hljs-string\">\"5432\"<\/span>,\n                                  database=<span class=\"hljs-string\">\"postgres_db\"<\/span>)\n\n    <span class=\"hljs-comment\"># Create a cursor to perform database operations<\/span>\n    cursor = connection.cursor()\n    <span class=\"hljs-comment\"># Print PostgreSQL details<\/span>\n    print(<span class=\"hljs-string\">\"PostgreSQL server information\"<\/span>)\n    print(connection.get_dsn_parameters(), <span class=\"hljs-string\">\"\\n\"<\/span>)\n    <span class=\"hljs-comment\"># Executing a SQL query<\/span>\n    cursor.execute(<span class=\"hljs-string\">\"SELECT version();\"<\/span>)\n    <span class=\"hljs-comment\"># Fetch result<\/span>\n    record = cursor.fetchone()\n    print(<span class=\"hljs-string\">\"You are connected to - \"<\/span>, record, <span class=\"hljs-string\">\"\\n\"<\/span>)\n\n<span class=\"hljs-keyword\">except<\/span> (Exception, Error) <span class=\"hljs-keyword\">as<\/span> error:\n    print(<span class=\"hljs-string\">\"Error while connecting to PostgreSQL\"<\/span>, error)\n<span class=\"hljs-keyword\">finally<\/span>:\n    <span class=\"hljs-keyword\">if<\/span> (connection):\n        cursor.close()\n        connection.close()\n        print(<span class=\"hljs-string\">\"PostgreSQL connection is closed\"<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><button class=\"hljs-copy-button\" onclick=\"copy_code('code2', this);\"><i class=\"far fa-copy1\" aria-hidden=\"true\"><\/i><\/button><\/div>\n\n\n<p>You should get the following <strong>output<\/strong> after connecting to PostgreSQL from Python<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">PostgreSQL server information\n{'user': 'postgres', 'dbname': 'python_db', 'host': '127.0.0.1', 'port': '5432', 'tty': '', 'options': '', 'sslmode': 'prefer', 'sslcompression': '0', 'krbsrvname': 'postgres', 'target_session_attrs': 'any'} \n\nYou are connected to -  ('PostgreSQL 12.2) \nPostgreSQL connection is closed<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-important-points\"><strong>Important points <\/strong><\/h3>\n\n\n\n<ul><li>In our example, we are executing a&nbsp;<code>SELECT version();<\/code>&nbsp;query to fetch&nbsp;the PostgreSQL version.<\/li><li>Using the <code>Error<\/code> 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.<\/li><li>The error class helps us to understand the error in detail. It returns an error message and error code if any.<\/li><li>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.<\/li><li>Cursors are not thread-safe.<\/li><li>We can retrieve query result using cursor methods such as <code>fetchone()<\/code>, <code>fetchmany()<\/code>, <code>fetcthall()<\/code>.<\/li><\/ul>\n\n\n\n<p><strong>try-except-finally block<\/strong><\/p>\n\n\n\n<ul><li>We placed all our code in the try-except block to catch the database exceptions and errors that may occur during this process.<\/li><\/ul>\n\n\n\n<p><strong><code>cursor.close()<\/code>&nbsp; and <code>connection.close()<\/code><\/strong><\/p>\n\n\n\n<ul><li>It is always good practice to close the cursor and connection object once your work gets completed to avoid database issues.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-create-a-postgresql-table-from-python\">Create a PostgreSQL table from Python<\/h2>\n\n\n\n<p>This section will learn how to create a table in PostgreSQL from Python. In this example, we will create a &#8220;<strong>Mobile<\/strong>&#8221; table in PostgreSQL.<\/p>\n\n\n<div class=\"hljstoolbar\"><pre id=\"code3\"  id=\"norun\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\"><span class=\"hljs-keyword\">import<\/span> psycopg2\n<span class=\"hljs-keyword\">from<\/span> psycopg2 <span class=\"hljs-keyword\">import<\/span> Error\n\n<span class=\"hljs-keyword\">try<\/span>:\n    connection = psycopg2.connect(user=<span class=\"hljs-string\">\"postgres\"<\/span>,\n                                  password=<span class=\"hljs-string\">\"pynative@#29\"<\/span>,\n                                  host=<span class=\"hljs-string\">\"127.0.0.1\"<\/span>,\n                                  port=<span class=\"hljs-string\">\"5432\"<\/span>,\n                                  database=<span class=\"hljs-string\">\"postgres_db\"<\/span>)\n\n    cursor = connection.cursor()\n    <span class=\"hljs-comment\"># SQL query to create a new table<\/span>\n    create_table_query = <span class=\"hljs-string\">'''CREATE TABLE mobile\n          (ID INT PRIMARY KEY     NOT NULL,\n          MODEL           TEXT    NOT NULL,\n          PRICE         REAL); '''<\/span>\n    <span class=\"hljs-comment\"># Execute a command: this creates a new table<\/span>\n    cursor.execute(create_table_query)\n    connection.commit()\n    print(<span class=\"hljs-string\">\"Table created successfully in PostgreSQL \"<\/span>)\n\n<span class=\"hljs-keyword\">except<\/span> (Exception, Error) <span class=\"hljs-keyword\">as<\/span> error:\n    print(<span class=\"hljs-string\">\"Error while connecting to PostgreSQL\"<\/span>, error)\n<span class=\"hljs-keyword\">finally<\/span>:\n    <span class=\"hljs-keyword\">if<\/span> connection:\n        cursor.close()\n        connection.close()\n        print(<span class=\"hljs-string\">\"PostgreSQL connection is closed\"<\/span>)\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><button class=\"hljs-copy-button\" onclick=\"copy_code('code3', this);\"><i class=\"far fa-copy1\" aria-hidden=\"true\"><\/i><\/button><\/div>\n\n\n<p><strong>Output<\/strong><\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-1 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<pre class=\"wp-block-preformatted\">Table created successfully in PostgreSQL PostgreSQL connection is closed<\/pre>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\">\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"402\" height=\"242\" src=\"https:\/\/pynative.com\/wp-content\/uploads\/2020\/12\/create_postgresql_table_from_python.jpg\" alt=\"Create a PostgreSQL table from Python\" class=\"wp-image-7601\" srcset=\"https:\/\/pynative.com\/wp-content\/uploads\/2020\/12\/create_postgresql_table_from_python.jpg 402w, https:\/\/pynative.com\/wp-content\/uploads\/2020\/12\/create_postgresql_table_from_python-300x181.jpg 300w\" sizes=\"(max-width: 402px) 100vw, 402px\" \/><figcaption>Create a PostgreSQL table from Python<\/figcaption><\/figure><\/div>\n<\/div>\n<\/div>\n\n\n\n<p><strong>Note<\/strong>: Note: In the end, we are committing our changes to the database using the <code>commit()<\/code> method.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-mapping-between-python-and-postgresql-types\">The mapping between Python and PostgreSQL types<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th><strong>Python<\/strong><\/th><th><strong>PostgreSQL<\/strong><\/th><\/tr><\/thead><tbody><tr><td><code>None<\/code><\/td><td><code>NULL<\/code><\/td><\/tr><tr><td><code>bool<\/code><\/td><td><code>bool<\/code><\/td><\/tr><tr><td><code>float<\/code><\/td><td><code>real<\/code> or <code>double<\/code><\/td><\/tr><tr><td><code>int<\/code><\/td><td><code>smallint<\/code><br><code>integer<\/code><br><code>bigint<\/code><\/td><\/tr><tr><td><code>Decimal<\/code><\/td><td><code>numeric<\/code><\/td><\/tr><tr><td><code>str<\/code><\/td><td><code>varchar<\/code><br><code>text<\/code><\/td><\/tr><tr><td><code>date<\/code><\/td><td><code>date<\/code><\/td><\/tr><tr><td><code>time<\/code><\/td><td><code>time<\/code><br><code>timetz<\/code><\/td><\/tr><tr><td><code>datetime<\/code><\/td><td><code>timestamp<\/code><br><code>timestamptz<\/code><\/td><\/tr><tr><td><code>timedelta<\/code><\/td><td><code>interval<\/code><\/td><\/tr><tr><td><code>list<\/code><\/td><td><code>ARRAY<\/code><\/td><\/tr><tr><td><code>tuple<\/code><\/td><td>Composite types<br>IN syntax<\/td><\/tr><tr><td><code>dict<\/code><\/td><td><code>hstore<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-constants-and-numeric-conversion\">Constants and numeric conversion<\/h3>\n\n\n\n<p>When you try to insert Python <code>None<\/code> and <code>boolean<\/code> values such as <code>True<\/code> and <code>False<\/code> 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.<\/p>\n\n\n\n<p>For example, When you execute an insert query, Python numeric objects such as <code>int<\/code>, <code>long<\/code>, <code>float<\/code>, <code>Decimal <\/code>are converted into a PostgreSQL numerical representation. When you read from the PostgreSQL table, integer types are converted into&nbsp;an <code>int<\/code>, floating-point types are converted into&nbsp;a <code>float<\/code>, numeric\/Decimal are converted into&nbsp;<code>Decimal<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-perform-postgresql-crud-operations-from-python\">Perform PostgreSQL CRUD operations from Python<\/h2>\n\n\n\n<p>Now, we created a &#8220;<strong>mobile<\/strong>&#8221; table. Now let&#8217; see how to perform insert, select, update, and delete PostgreSQL queries from Python. <\/p>\n\n\n\n<p>In this section, We will learn how to <a href=\"https:\/\/pynative.com\/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations\/\">perform PostgreSQL CRUD operations from Python<\/a>.<\/p>\n\n\n\n<p>Now, Let&#8217;s see the example.<\/p>\n\n\n<div class=\"hljstoolbar\"><pre id=\"code4\"  id=\"norun\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\"><span class=\"hljs-keyword\">import<\/span> psycopg2\n\n<span class=\"hljs-keyword\">try<\/span>:\n    connection = psycopg2.connect(user=<span class=\"hljs-string\">\"postgres\"<\/span>,\n                                  password=<span class=\"hljs-string\">\"pynative@#29\"<\/span>,\n                                  host=<span class=\"hljs-string\">\"127.0.0.1\"<\/span>,\n                                  port=<span class=\"hljs-string\">\"5432\"<\/span>,\n                                  database=<span class=\"hljs-string\">\"postgres_db\"<\/span>)\n\n    cursor = connection.cursor()\n    <span class=\"hljs-comment\"># Executing a SQL query to insert data into  table<\/span>\n    insert_query = <span class=\"hljs-string\">\"\"\" INSERT INTO mobile (ID, MODEL, PRICE) VALUES (1, 'Iphone12', 1100)\"\"\"<\/span>\n    cursor.execute(insert_query)\n    connection.commit()\n    print(<span class=\"hljs-string\">\"1 Record inserted successfully\"<\/span>)\n    <span class=\"hljs-comment\"># Fetch result<\/span>\n    cursor.execute(<span class=\"hljs-string\">\"SELECT * from mobile\"<\/span>)\n    record = cursor.fetchall()\n    print(<span class=\"hljs-string\">\"Result \"<\/span>, record)\n\n    <span class=\"hljs-comment\"># Executing a SQL query to update table<\/span>\n    update_query = <span class=\"hljs-string\">\"\"\"Update mobile set price = 1500 where id = 1\"\"\"<\/span>\n    cursor.execute(update_query)\n    connection.commit()\n    count = cursor.rowcount\n    print(count, <span class=\"hljs-string\">\"Record updated successfully \"<\/span>)\n    <span class=\"hljs-comment\"># Fetch result<\/span>\n    cursor.execute(<span class=\"hljs-string\">\"SELECT * from mobile\"<\/span>)\n    print(<span class=\"hljs-string\">\"Result \"<\/span>, cursor.fetchall())\n\n    <span class=\"hljs-comment\"># Executing a SQL query to delete table<\/span>\n    delete_query = <span class=\"hljs-string\">\"\"\"Delete from mobile where id = 1\"\"\"<\/span>\n    cursor.execute(delete_query)\n    connection.commit()\n    count = cursor.rowcount\n    print(count, <span class=\"hljs-string\">\"Record deleted successfully \"<\/span>)\n    <span class=\"hljs-comment\"># Fetch result<\/span>\n    cursor.execute(<span class=\"hljs-string\">\"SELECT * from mobile\"<\/span>)\n    print(<span class=\"hljs-string\">\"Result \"<\/span>, cursor.fetchall())\n\n\n<span class=\"hljs-keyword\">except<\/span> (Exception, psycopg2.Error) <span class=\"hljs-keyword\">as<\/span> error:\n    print(<span class=\"hljs-string\">\"Error while connecting to PostgreSQL\"<\/span>, error)\n<span class=\"hljs-keyword\">finally<\/span>:\n    <span class=\"hljs-keyword\">if<\/span> connection:\n        cursor.close()\n        connection.close()\n        print(<span class=\"hljs-string\">\"PostgreSQL connection is closed\"<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><button class=\"hljs-copy-button\" onclick=\"copy_code('code4', this);\"><i class=\"far fa-copy1\" aria-hidden=\"true\"><\/i><\/button><\/div>\n\n\n<p><strong>Output<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">1 Record inserted successfully\nResult  [(1, 'Iphone12', 1100.0)]\n1 Record updated successfully \nResult  [(1, 'Iphone12', 1500.0)]\n1 Record deleted successfully \nResult  []\nPostgreSQL connection is closed<\/pre>\n\n\n\n<p>Please refer to the following tutorials to have more information on insert, update, and delete data from the PostgreSQL table using Python.<\/p>\n\n\n\n<p>In the following tutorial, we will teach you how to <strong>pass parameters to SQL queries<\/strong>.&nbsp; We will learn how to use a <strong>parameterized query<\/strong> to pass Python variables and dynamic data into SQL queries.<\/p>\n\n\n\n<ul><li><a href=\"https:\/\/pynative.com\/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations\/#h-python-postgresql-insert-into-database-table\">Insert data into the PostgreSQL Table from Python<\/a>: Learn how to execute the SQL insert query from a Python application to add a record to the PostgreSQL table.<\/li><li><a href=\"https:\/\/pynative.com\/python-postgresql-select-data-from-table\/\">Select data from PostgreSQL Table from Python<\/a>: Learn how to execute a SQL select query from a Python application to fetch rows from the database table. We will also learn how to use <a href=\"https:\/\/pynative.com\/python-cursor-fetchall-fetchmany-fetchone-to-read-rows-from-table\/\">fetchall()<\/a>, <code>fetchmany()<\/code> and <code>fetchone()<\/code> methods to read a limited number of rows from the table.<\/li><li><a href=\"https:\/\/pynative.com\/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations\/#h-python-postgresql-update-table-data\">Update data of PostgreSQL table from Python<\/a>: Learn how to execute SQL update query from Python application to modify a PostgreSQL table&#8217;s records.<\/li><li><a href=\"https:\/\/pynative.com\/python-postgresql-insert-update-delete-table-data-to-perform-crud-operations\/#h-python-postgresql-delete-table-row-and-columns\">Delete data from PostgreSQL table from Python<\/a>: Learn how to execute SQL delete query from Python application to delete records from a PostgreSQL table.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-working-with-postgresql-date-and-time-in-python\">Working with PostgreSQL date and time in Python<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In a usual scenario, when you execute the insert query with the <code>datetime<\/code> object, the Python psycopg2 module converts it into a PostgreSQL <code>timestamp<\/code> format to insert it in the table.<\/p>\n\n\n\n<p>And when you execute a SELECT query from Python to read <code>timestamp<\/code> values from the PostgreSQL table, the psycopg2 module converts it into a <code>datetime<\/code> object.<\/p>\n\n\n\n<p>We are using the &#8220;<strong>Item<\/strong>&#8221; table for this demo. Please copy and execute the below query on your PostgreSQL query tool to have adequate data for this operation.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\">CREATE TABLE item ( \n\titem_id serial NOT NULL PRIMARY KEY, \n\titem_name VARCHAR (<span class=\"hljs-number\">100<\/span>) NOT NULL, \n\tpurchase_time timestamp NOT NULL,\n\tprice INTEGER NOT NULL\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Let\u2019s understand this scenario with a simple example. Here we will read <code>purchase_time<\/code> column from the PostgreSQL table and convert it into a Python datetime object.<\/p>\n\n\n<div class=\"hljstoolbar\"><pre id=\"code5\"  id=\"norun\" class=\"wp-block-code language-python\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"Python\" data-shcb-language-slug=\"python\"><span><code class=\"hljs language-python\"><span class=\"hljs-keyword\">import<\/span> datetime\n\n<span class=\"hljs-keyword\">import<\/span> psycopg2\n\n<span class=\"hljs-keyword\">try<\/span>:\n    connection = psycopg2.connect(user=<span class=\"hljs-string\">\"postgres\"<\/span>,\n                                  password=<span class=\"hljs-string\">\"pynative@#29\"<\/span>,\n                                  host=<span class=\"hljs-string\">\"127.0.0.1\"<\/span>,\n                                  port=<span class=\"hljs-string\">\"5432\"<\/span>,\n                                  database=<span class=\"hljs-string\">\"postgres_db\"<\/span>)\n\n    cursor = connection.cursor()\n    <span class=\"hljs-comment\"># Executing a SQL query to insert datetime into table<\/span>\n    insert_query = <span class=\"hljs-string\">\"\"\" INSERT INTO item (item_Id, item_name, purchase_time, price) VALUES (%s, %s, %s, %s)\"\"\"<\/span>\n    item_purchase_time = datetime.datetime.now()\n    item_tuple = (<span class=\"hljs-number\">12<\/span>, <span class=\"hljs-string\">\"Keyboard\"<\/span>, item_purchase_time, <span class=\"hljs-number\">150<\/span>)\n    cursor.execute(insert_query, item_tuple)\n    connection.commit()\n    print(<span class=\"hljs-string\">\"1 item inserted successfully\"<\/span>)\n\n    <span class=\"hljs-comment\"># Read PostgreSQL purchase timestamp value into Python datetime<\/span>\n    cursor.execute(<span class=\"hljs-string\">\"SELECT purchase_time from item where item_id = 12\"<\/span>)\n    purchase_datetime = cursor.fetchone()\n    print(<span class=\"hljs-string\">\"Item Purchase date is  \"<\/span>, purchase_datetime&#91;<span class=\"hljs-number\">0<\/span>].date())\n    print(<span class=\"hljs-string\">\"Item Purchase time is  \"<\/span>, purchase_datetime&#91;<span class=\"hljs-number\">0<\/span>].time())\n\n<span class=\"hljs-keyword\">except<\/span> (Exception, psycopg2.Error) <span class=\"hljs-keyword\">as<\/span> error:\n    print(<span class=\"hljs-string\">\"Error while connecting to PostgreSQL\"<\/span>, error)\n<span class=\"hljs-keyword\">finally<\/span>:\n    <span class=\"hljs-keyword\">if<\/span> connection:\n        cursor.close()\n        connection.close()\n        print(<span class=\"hljs-string\">\"PostgreSQL connection is closed\"<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">Python<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">python<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre><button class=\"hljs-copy-button\" onclick=\"copy_code('code5', this);\"><i class=\"far fa-copy1\" aria-hidden=\"true\"><\/i><\/button><\/div>\n\n\n<p><strong>Output<\/strong>:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">1 item inserted successfully\nItem Purchase date is   2020-12-14\nItem Purchase time is   12:47:45.854942\nPostgreSQL connection is closed<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-call-postgresql-function-and-stored-procedure-from-python\">Call PostgreSQL Function and Stored Procedure from Python<\/h2>\n\n\n\n<p>PostgreSQL function and the Stored procedure can perform different operations, such as data manipulation or data retrieval. We can execute such functions from Python.<\/p>\n\n\n\n<p>Learn how to <a href=\"https:\/\/pynative.com\/python-execute-postgresql-stored-procedure-and-function\/\">execute the PostgreSQL function and Stored procedure in Python<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-python-postgresql-transaction-management\">Python PostgreSQL Transaction management<\/h2>\n\n\n\n<p>In this article, we will see how to <a href=\"https:\/\/pynative.com\/python-postgresql-transaction-management-using-commit-and-rollback\/\">manage PostgreSQL transactions from Python<\/a> using psycopg2.<\/p>\n\n\n\n<ul><li>Learn how to use the <code>commit()<\/code> and the <code>rollback()<\/code> method of a <code>connection<\/code> class to manage database transactions and maintain the ACID properties.<\/li><li>Also, learn how to change the PostgreSQL transaction isolation level from Python.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-python-postgresql-connection-pooling\">Python PostgreSQL Connection Pooling<\/h2>\n\n\n\n<p>This section will let you know what a connection pool is and how to <a href=\"https:\/\/pynative.com\/psycopg2-python-postgresql-connection-pooling\/\">implement a PostgreSQL database connection pool<\/a> using Psycopg2 in Python. Using Psycopg2, we can implement a connection pool for simple as well as multithreaded applications.<\/p>\n\n\n\n<p>Use the Connection pool to increase the speed and performance of database-centric applications. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-python-postgresql-exercise-project\">Python PostgreSQL Exercise Project<\/h2>\n\n\n\n<p>Solve our free <a href=\"https:\/\/pynative.com\/python-database-programming-exercise-with-solution\/\"><strong>Python database exercise project<\/strong><\/a> to practice and master the PostgreSQL database operations using&nbsp;Python.<\/p>\n\n\n\n<p>In this exercise project, We will implement the <strong>Hospital Information System<\/strong>, which <strong>covers all database operations<\/strong>. In this Python database exercise, we will do database CRUD operations From Python. This practice exercise also covers&nbsp;transaction management&nbsp;and&nbsp;error-handling techniques.<\/p>\n\n\n\n<p>Reference:<\/p>\n\n\n\n<ul><li><a href=\"https:\/\/www.psycopg.org\/docs\/\" target=\"_blank\" rel=\"noreferrer noopener\">Psycopg2<\/a><\/li><li><a href=\"https:\/\/www.postgresql.org\/\">PostgreSQL<\/a><\/li><li><a href=\"https:\/\/www.python.org\/dev\/peps\/pep-0249\/\" target=\"_blank\" rel=\"noreferrer noopener\">PEP 249<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This Python PostgreSQL tutorial demonstrates how to use the Psycopg2 module to connect to PostgreSQL and perform SQL queries, database operations. There are many ways we can connect to a PostgreSQL database from Python, and in this tutorial, we&#8217;re going to explore several options to see how to achieve this. Below is the list of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":2121,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_genesis_hide_title":false,"_genesis_hide_breadcrumbs":false,"_genesis_hide_singular_image":false,"_genesis_hide_footer_widgets":false,"_genesis_custom_body_class":"","_genesis_custom_post_class":"","_genesis_layout":"","footnotes":""},"categories":[22,23],"tags":[26],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v22.4 (Yoast SEO v22.4) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Python PostgreSQL Tutorial Using Psycopg2 [Complete Guide]<\/title>\n<meta name=\"description\" content=\"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/pynative.com\/python-postgresql-tutorial\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Python PostgreSQL Tutorial Using Psycopg2\" \/>\n<meta property=\"og:description\" content=\"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/pynative.com\/python-postgresql-tutorial\/\" \/>\n<meta property=\"og:site_name\" content=\"PYnative\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-09T07:20:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-09T05:18:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"297\" \/>\n\t<meta property=\"og:image:height\" content=\"231\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishal\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@PyNative\" \/>\n<meta name=\"twitter:site\" content=\"@PyNative\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/\"},\"author\":{\"name\":\"Vishal\",\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f\"},\"headline\":\"Python PostgreSQL Tutorial Using Psycopg2\",\"datePublished\":\"2018-08-09T07:20:48+00:00\",\"dateModified\":\"2021-03-09T05:18:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/\"},\"wordCount\":1797,\"commentCount\":42,\"publisher\":{\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f\"},\"image\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png\",\"keywords\":[\"Python PostgreSQL\"],\"articleSection\":[\"Python\",\"Python Databases\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/pynative.com\/python-postgresql-tutorial\/#respond\"]}],\"accessibilityFeature\":[\"tableOfContents\"]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/\",\"name\":\"Python PostgreSQL Tutorial Using Psycopg2 [Complete Guide]\",\"isPartOf\":{\"@id\":\"https:\/\/pynative.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png\",\"datePublished\":\"2018-08-09T07:20:48+00:00\",\"dateModified\":\"2021-03-09T05:18:04+00:00\",\"description\":\"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.\",\"breadcrumb\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/pynative.com\/python-postgresql-tutorial\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage\",\"url\":\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png\",\"contentUrl\":\"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png\",\"width\":297,\"height\":231,\"caption\":\"Python PostgreSQL\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/pynative.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Python\",\"item\":\"https:\/\/pynative.com\/python\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Databases\",\"item\":\"https:\/\/pynative.com\/python\/databases\/\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Python PostgreSQL Tutorial Using Psycopg2\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/pynative.com\/#website\",\"url\":\"https:\/\/pynative.com\/\",\"name\":\"PYnative\",\"description\":\"Python Programming\",\"publisher\":{\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/pynative.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f\",\"name\":\"Vishal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/pynative.com\/wp-content\/uploads\/2019\/01\/vishalHule.jpg\",\"contentUrl\":\"https:\/\/pynative.com\/wp-content\/uploads\/2019\/01\/vishalHule.jpg\",\"width\":968,\"height\":1065,\"caption\":\"Vishal\"},\"logo\":{\"@id\":\"https:\/\/pynative.com\/#\/schema\/person\/image\/\"},\"description\":\"Founder of PYnative.com. I am a Python developer and I love to write articles to help developers. | All the best for your future Python endeavors!\",\"sameAs\":[\"https:\/\/pynative.com\"]},{\"@type\":\"HowTo\",\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#howto-1\",\"name\":\"Python PostgreSQL Tutorial Using Psycopg2\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#article\"},\"description\":\"How to Connect to PostgreSQL in Python\",\"step\":[{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938393338\",\"name\":\"Install Psycopg2 module\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"Install and import psycopg2 module. Import using a import psycopg2 statement so you can use this module's methods to communicate with the PostgreSQL database.\"}]},{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938566138\",\"name\":\"Use the connect() method\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"Use the psycopg2.connect() method with the required arguments to connect MySQL. It would return an Connection object if the connection established successfully\"}]},{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612939866983\",\"name\":\"Use the cursor() method\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python.\"}]},{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938641024\",\"name\":\"Use the execute() method\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"The execute() methods run the SQL query and return the result.\"}]},{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938710846\",\"name\":\"Extract result using fetchall()\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"Use cursor.fetchall() or fetchone() or fetchmany() to read query result.\"}]},{\"@type\":\"HowToStep\",\"url\":\"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938784560\",\"name\":\"Close cursor and connection objects\",\"itemListElement\":[{\"@type\":\"HowToDirection\",\"text\":\"use cursor.clsoe() and connection.clsoe() method to close PostgreSQL connections after your work completes\"}]}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Python PostgreSQL Tutorial Using Psycopg2 [Complete Guide]","description":"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/pynative.com\/python-postgresql-tutorial\/","og_locale":"en_US","og_type":"article","og_title":"Python PostgreSQL Tutorial Using Psycopg2","og_description":"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.","og_url":"https:\/\/pynative.com\/python-postgresql-tutorial\/","og_site_name":"PYnative","article_published_time":"2018-08-09T07:20:48+00:00","article_modified_time":"2021-03-09T05:18:04+00:00","og_image":[{"width":297,"height":231,"url":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","type":"image\/png"}],"author":"Vishal","twitter_card":"summary_large_image","twitter_creator":"@PyNative","twitter_site":"@PyNative","twitter_misc":{"Written by":"Vishal","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#article","isPartOf":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/"},"author":{"name":"Vishal","@id":"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f"},"headline":"Python PostgreSQL Tutorial Using Psycopg2","datePublished":"2018-08-09T07:20:48+00:00","dateModified":"2021-03-09T05:18:04+00:00","mainEntityOfPage":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/"},"wordCount":1797,"commentCount":42,"publisher":{"@id":"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f"},"image":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage"},"thumbnailUrl":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","keywords":["Python PostgreSQL"],"articleSection":["Python","Python Databases"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/pynative.com\/python-postgresql-tutorial\/#respond"]}],"accessibilityFeature":["tableOfContents"]},{"@type":"WebPage","@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/","name":"Python PostgreSQL Tutorial Using Psycopg2 [Complete Guide]","isPartOf":{"@id":"https:\/\/pynative.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage"},"image":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage"},"thumbnailUrl":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","datePublished":"2018-08-09T07:20:48+00:00","dateModified":"2021-03-09T05:18:04+00:00","description":"Connect to PostgreSQL database from Python using Psycopg2. Select, Insert, update, delete PostgreSQL data from Python. execute stored prodcudure and manage PostgreSQL transction from Python.","breadcrumb":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/pynative.com\/python-postgresql-tutorial\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#primaryimage","url":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","contentUrl":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","width":297,"height":231,"caption":"Python PostgreSQL"},{"@type":"BreadcrumbList","@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/pynative.com\/"},{"@type":"ListItem","position":2,"name":"Python","item":"https:\/\/pynative.com\/python\/"},{"@type":"ListItem","position":3,"name":"Databases","item":"https:\/\/pynative.com\/python\/databases\/"},{"@type":"ListItem","position":4,"name":"Python PostgreSQL Tutorial Using Psycopg2"}]},{"@type":"WebSite","@id":"https:\/\/pynative.com\/#website","url":"https:\/\/pynative.com\/","name":"PYnative","description":"Python Programming","publisher":{"@id":"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/pynative.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/pynative.com\/#\/schema\/person\/64b55d5bde2265918c5a9931de4de71f","name":"Vishal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/pynative.com\/#\/schema\/person\/image\/","url":"https:\/\/pynative.com\/wp-content\/uploads\/2019\/01\/vishalHule.jpg","contentUrl":"https:\/\/pynative.com\/wp-content\/uploads\/2019\/01\/vishalHule.jpg","width":968,"height":1065,"caption":"Vishal"},"logo":{"@id":"https:\/\/pynative.com\/#\/schema\/person\/image\/"},"description":"Founder of PYnative.com. I am a Python developer and I love to write articles to help developers. | All the best for your future Python endeavors!","sameAs":["https:\/\/pynative.com"]},{"@type":"HowTo","@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#howto-1","name":"Python PostgreSQL Tutorial Using Psycopg2","mainEntityOfPage":{"@id":"https:\/\/pynative.com\/python-postgresql-tutorial\/#article"},"description":"How to Connect to PostgreSQL in Python","step":[{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938393338","name":"Install Psycopg2 module","itemListElement":[{"@type":"HowToDirection","text":"Install and import psycopg2 module. Import using a import psycopg2 statement so you can use this module's methods to communicate with the PostgreSQL database."}]},{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938566138","name":"Use the connect() method","itemListElement":[{"@type":"HowToDirection","text":"Use the psycopg2.connect() method with the required arguments to connect MySQL. It would return an Connection object if the connection established successfully"}]},{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612939866983","name":"Use the cursor() method","itemListElement":[{"@type":"HowToDirection","text":"Create a cursor object using the connection object returned by the connect method to execute PostgreSQL queries from Python."}]},{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938641024","name":"Use the execute() method","itemListElement":[{"@type":"HowToDirection","text":"The execute() methods run the SQL query and return the result."}]},{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938710846","name":"Extract result using fetchall()","itemListElement":[{"@type":"HowToDirection","text":"Use cursor.fetchall() or fetchone() or fetchmany() to read query result."}]},{"@type":"HowToStep","url":"https:\/\/pynative.com\/python-postgresql-tutorial\/#how-to-step-1612938784560","name":"Close cursor and connection objects","itemListElement":[{"@type":"HowToDirection","text":"use cursor.clsoe() and connection.clsoe() method to close PostgreSQL connections after your work completes"}]}],"inLanguage":"en-US"}]}},"featured_image_src":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","featured_image_src_square":"https:\/\/pynative.com\/wp-content\/uploads\/2018\/08\/python_postgresql-1.png","author_info":{"display_name":"Vishal","author_link":"https:\/\/pynative.com\/author\/vishal\/"},"_links":{"self":[{"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/posts\/467"}],"collection":[{"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/comments?post=467"}],"version-history":[{"count":0,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/posts\/467\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/media\/2121"}],"wp:attachment":[{"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/media?parent=467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/categories?post=467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pynative.com\/wp-json\/wp\/v2\/tags?post=467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}