Category: python Postgresql

Python PostgreSQL – Create Table ”; Previous Next You can create a new table in a database in PostgreSQL using the CREATE TABLE statement. While executing this you need to specify the name of the table, column names and their data types. Syntax Following is the syntax of the CREATE TABLE statement in PostgreSQL. CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ….. columnN datatype, ); Example Following example creates a table with name CRICKETERS in PostgreSQL. postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age INT, Place_Of_Birth VARCHAR(255), Country VARCHAR(255)); CREATE TABLE postgres=# You can get the list of tables in a database in PostgreSQL using the dt command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as follows − postgres=# dt List of relations Schema | Name | Type | Owner ——–+————+——-+———- public | cricketers | table | postgres (1 row) postgres=# In the same way, you can get the description of the created table using d as shown below − postgres=# d cricketers Table “public.cricketers” Column | Type | Collation | Nullable | Default —————-+————————+———–+———-+——— first_name | character varying(255) | | | last_name | character varying(255) | | | age | integer | | | place_of_birth | character varying(255) | | | country | character varying(255) | | | postgres=# Creating a Table Using Python To create a table using python you need to execute the CREATE TABLE statement using the execute() method of the Cursor of pyscopg2. Example The following Python example creates a table with name employee. import psycopg2 #Establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating table as per requirement sql =”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT)””” cursor.execute(sql) print(“Table created successfully……..”) #Closing the connection conn.close() Output Table created successfully…….. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Create Database ”; Previous Next You can create a database in PostgreSQL using the CREATE DATABASE statement. You can execute this statement in PostgreSQL shell prompt by specifying the name of the database to be created after the command. Syntax Following is the syntax of the CREATE DATABASE statement. CREATE DATABASE dbname; Example Following statement creates a database named testdb in PostgreSQL. postgres=# CREATE DATABASE testdb; CREATE DATABASE You can list out the database in PostgreSQL using the l command. If you verify the list of databases, you can find the newly created database as follows − postgres=# l List of databases Name | Owner | Encoding | Collate | Ctype | ———–+———-+———-+—————————-+————-+ mydb | postgres | UTF8 | English_United States.1252 | ……….. | postgres | postgres | UTF8 | English_United States.1252 | ……….. | template0 | postgres | UTF8 | English_United States.1252 | ……….. | template1 | postgres | UTF8 | English_United States.1252 | ……….. | testdb | postgres | UTF8 | English_United States.1252 | ……….. | (5 rows) You can also create a database in PostgreSQL from command prompt using the command createdb, a wrapper around the SQL statement CREATE DATABASE. C:Program FilesPostgreSQL11bin> createdb -h localhost -p 5432 -U postgres sampledb Password: Creating a Database Using Python The cursor class of psycopg2 provides various methods execute various PostgreSQL commands, fetch records and copy data. You can create a cursor object using the cursor() method of the Connection class. The execute() method of this class accepts a PostgreSQL query as a parameter and executes it. Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE query using this method. Example Following python example creates a database named mydb in PostgreSQL database. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Preparing query to create a database sql = ”””CREATE database mydb”””; #Creating a database cursor.execute(sql) print(“Database created successfully……..”) #Closing the connection conn.close() Output Database created successfully…….. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Insert Data ”; Previous Next You can insert record into an existing table in PostgreSQL using the INSERT INTO statement. While executing this, you need to specify the name of the table, and values for the columns in it. Syntax Following is the recommended syntax of the INSERT statement − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); Where, column1, column2, column3,.. are the names of the columns of a table, and value1, value2, value3,… are the values you need to insert into the table. Example Assume we have created a table with name CRICKETERS using the CREATE TABLE statement as shown below − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age INT, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# Following PostgreSQL statement inserts a row in the above created table − postgres=# insert into CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) values (”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# While inserting records using the INSERT INTO statement, if you skip any columns names Record will be inserted leaving empty spaces at columns which you have skipped. postgres=# insert into CRICKETERS (First_Name, Last_Name, Country) values(”Jonathan”, ”Trott”, ”SouthAfrica”); INSERT 0 1 You can also insert records into a table without specifying the column names, if the order of values you pass is same as their respective column names in the table. postgres=# insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 postgres=# After inserting the records into a table you can verify its contents using the SELECT statement as shown below − postgres=# SELECT * from CRICKETERS; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | | | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India (5 rows) Inserting Data Using Python The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it. Therefore, to insert data into a table in PostgreSQL using python − Import psycopg2 package. Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Turn off the auto-commit mode by setting false as value to the attribute autocommit. The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method.. Then, execute the INSERT statement(s) by passing it/them as a parameter to the execute() method. Example Following Python program creates a table with name EMPLOYEE in PostgreSQL database and inserts records into it using the execute() method − import psycopg2 #Establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() # Preparing SQL queries to INSERT a record into the database. cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama priya”, 27, ”F”, 9000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Vinay”, ”Battacharya”, 20, ”M”, 6000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sharukh”, ”Sheik”, 25, ”M”, 8300)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Sarmista”, ”Sharma”, 26, ”F”, 10000)”””) cursor.execute(”””INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) # Commit your changes in the database conn.commit() print(“Records inserted……..”) # Closing the connection conn.close() Output Records inserted…….. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Database Connection ”; Previous Next PostgreSQL provides its own shell to execute queries. To establish connection with the PostgreSQL database, make sure that you have installed it properly in your system. Open the PostgreSQL shell prompt and pass details like Server, Database, username, and password. If all the details you have given are appropriate, a connection is established with PostgreSQL database. While passing the details you can go with the default server, database, port and, user name suggested by the shell. Establishing Connection Using Python The connection class of the psycopg2 represents/handles an instance of a connection. You can create new connections using the connect() function. This accepts the basic connection parameters such as dbname, user, password, host, port and returns a connection object. Using this function, you can establish a connection with the PostgreSQL. Example The following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. The name of the default database of PostgreSQL is postrgre. Therefore, we are supplying it as the database name. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”postgres”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Executing an MYSQL function using the execute() method cursor.execute(“select version()”) #Fetch a single row using fetchone() method. data = cursor.fetchone() print(“Connection established to: “,data) #Closing the connection conn.close() Connection established to: ( ”PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit”, ) Output Connection established to: ( ”PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit”, ) Print Page Previous Next Advertisements ”;

Python PostgreSQL – Update Table ”; Previous Next You can modify the contents of existing records of a table in PostgreSQL using the UPDATE statement. To update specific rows, you need to use the WHERE clause along with it. Syntax Following is the syntax of the UPDATE statement in PostgreSQL − UPDATE table_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following statement modifies the age of the cricketer, whose first name is Shikhar − postgres=# UPDATE CRICKETERS SET AGE = 45 WHERE FIRST_NAME = ”Shikhar” ; UPDATE 1 postgres=# If you retrieve the record whose FIRST_NAME is Shikhar you observe that the age value has been changed to 45 − postgres=# SELECT * FROM CRICKETERS WHERE FIRST_NAME = ”Shikhar”; first_name | last_name | age | place_of_birth | country ————+———–+—–+—————-+——— Shikhar | Dhawan | 45 | Delhi | India (1 row) postgres=# If you haven’t used the WHERE clause, values of all the records will be updated. Following UPDATE statement increases the age of all the records in the CRICKETERS table by 1 − postgres=# UPDATE CRICKETERS SET AGE = AGE+1; UPDATE 5 If you retrieve the contents of the table using SELECT command, you can see the updated values as − postgres=# SELECT * FROM CRICKETERS; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Jonathan | Trott | 39 | CapeTown | SouthAfrica Kumara | Sangakkara | 42 | Matale | Srilanka Virat | Kohli | 31 | Delhi | India Rohit | Sharma | 33 | Nagpur | India Shikhar | Dhawan | 46 | Delhi | India (5 rows) Updating Records Using Python The cursor class of psycopg2 provides a method with name execute() method. This method accepts the query as a parameter and executes it. Therefore, to insert data into a table in PostgreSQL using python − Import psycopg2 package. Create a connection object using the connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it. Turn off the auto-commit mode by setting false as value to the attribute autocommit. The cursor() method of the Connection class of the psycopg2 library returns a cursor object. Create a cursor object using this method. Then, execute the UPDATE statement by passing it as a parameter to the execute() method. Example Following Python code updates the contents of the Employee table and retrieves the results − import psycopg2 #establishing the connection conn = psycopg2.connect ( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Fetching all the rows before the update print(“Contents of the Employee table: “) sql = ”””SELECT * from EMPLOYEE””” cursor.execute(sql) print(cursor.fetchall()) #Updating the records sql = “UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ”M”” cursor.execute(sql) print(“Table updated…… “) #Fetching all the rows after the update print(“Contents of the Employee table after the update operation: “) sql = ”””SELECT * from EMPLOYEE””” cursor.execute(sql) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output Contents of the Employee table: [ (”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0) ] Table updated…… Contents of the Employee table after the update operation: [ (”Ramya”, ”Rama priya”, 27, ”F”, 9000.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0), (”Vinay”, ”Battacharya”, 21, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 26, ”M”, 8300.0) ] Print Page Previous Next Advertisements ”;

Python PostgreSQL – Where Clause ”; Previous Next While performing SELECT, UPDATE or, DELETE operations, you can specify condition to filter the records using the WHERE clause. The operation will be performed on the records which satisfies the given condition. Syntax Following is the syntax of the WHERE clause in PostgreSQL − SELECT column1, column2, columnN FROM table_name WHERE [search_condition] You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear. Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following SELECT statement retrieves the records whose age is greater than 35 − postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka (2 rows) postgres=# Where Clause Using Python To fetch specific records from a table using the python program execute the SELECT statement with WHERE clause, by passing it as a parameter to the execute() method. Example Following python example demonstrates the usage of WHERE command using python. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT)””” cursor.execute(sql) #Populating the table insert_stmt = “INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (%s, %s, %s, %s, %s)” data = [(”Krishna”, ”Sharma”, 19, ”M”, 2000), (”Raj”, ”Kandukuri”, 20, ”M”, 7000), (”Ramya”, ”Ramapriya”, 25, ”M”, 5000),(”Mac”, ”Mohan”, 26, ”M”, 2000)] cursor.executemany(insert_stmt, data) #Retrieving specific records using the where clause cursor.execute(“SELECT * from EMPLOYEE WHERE AGE <23″) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 19, ”M”, 2000.0), (”Raj”, ”Kandukuri”, 20, ”M”, 7000.0)] Print Page Previous Next Advertisements ”;

Python PostgreSQL Tutorial PDF Version Quick Guide Resources Job Search Discussion Python is a general-purpose interpreted, interactive, object-oriented, and high-level programming language. It was created by Guido van Rossum during 1985-1990. Like Perl, Python source code is also available under the GNU General Public License (GPL). This tutorial gives enough understanding on Python programming language. This tutorial explains how to communicate with PostgreSQL database in detail, along with examples. Audience This tutorial is designed for python programmers who would like to understand the psycog2 modules in detail. Prerequisites Before proceeding with this tutorial, you should have a good understanding of python programming language. It is also recommended to have basic understanding of the databases — PostgreSQL. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Introduction ”; Previous Next PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. To communicate with PostgreSQL using Python you need to install psycopg, an adapter provided for python programming, the current version of this is psycog2. psycopg2 was written with the aim of being very small and fast, and stable as a rock. It is available under PIP (package manager of python) Installing Psycog2 using PIP First of all, make sure python and PIP is installed in your system properly and, PIP is up-to-date. To upgrade PIP, open command prompt and execute the following command − C:UsersTutorialspoint>python -m pip install –upgrade pip Collecting pip Using cached https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl Installing collected packages: pip Found existing installation: pip 19.0.3 Uninstalling pip-19.0.3: Successfully uninstalled pip-19.0.3 Successfully installed pip-19.2.2 Then, open command prompt in admin mode and execute the pip install psycopg2-binary command as shown below − C:WINDOWSsystem32>pip install psycopg2-binary Collecting psycopg2-binary Using cached https://files.pythonhosted.org/packages/80/79/d0d13ce4c2f1addf4786f4a2ded802c2df66ddf3c1b1a982ed8d4cb9fc6d/psycopg2_binary-2.8.3-cp37-cp37m-win32.whl Installing collected packages: psycopg2-binary Successfully installed psycopg2-binary-2.8.3 Verification To verify the installation, create a sample python script with the following line in it. import mysql.connector If the installation is successful, when you execute it, you should not get any errors − D:Python_PostgreSQL>import psycopg2 D:Python_PostgreSQL> Print Page Previous Next Advertisements ”;