Python PostgreSQL Tutorial - Tutorialspoint

3y ago
241 Views
43 Downloads
916.25 KB
47 Pages
Last View : 12d ago
Last Download : 2m ago
Upload by : Aiyana Dorn
Transcription

Python PostgreSQL1

Python PostgreSQLAbout the TutorialPython is a general-purpose interpreted, interactive, object-oriented, and high-levelprogramming 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). Thistutorial gives enough understanding on Python programming language.This tutorial explains how to communicate with PostgreSQL database in detail, along withexamples.AudienceThis tutorial is designed for python programmers who would like to understand thepsycog2 modules in detail.PrerequisitesBefore proceeding with this tutorial, you should have a good understanding of pythonprogramming language. It is also recommended to have basic understanding of thedatabases — PostgreSQL.Copyright & Disclaimer Copyright 2020 by Tutorials Point (I) Pvt. Ltd.All the content and graphics published in this e-book are the property of Tutorials Point (I)Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republishany contents or a part of contents of this e-book in any manner without written consentof the publisher.We strive to update the contents of our website and tutorials as timely and as precisely aspossible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt.Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of ourwebsite or its contents including this tutorial. If you discover any errors on our website orin this tutorial, please notify us at contact@tutorialspoint.com2

Python PostgreSQLTable of ContentsAbout the Tutorial . 2Audience . 2Prerequisites . 2Copyright & Disclaimer . 2Table of Contents . 31.Python PostgreSQL ― Introduction . 52.Python PostgreSQL — Database Connection . 7Establishing connection using python . 73.Python PostgreSQL ― Create Database . 9Creating a database using python . 104.Python PostgreSQL - Create Table . 11Creating a table using python . 125.Python PostgreSQL — Insert Data . 14Inserting data using python . 156.Python PostgreSQL ― Select Data. 18Retrieving data using python. 197.Python PostgreSQL — Where Clause. 22Where clause using python . 238.Python PostgreSQL ― Order By . 25ORDER BY clause using python . 279.Python PostgreSQL — Update Table . 29Updating records using python . 3010. Python PostgreSQL ― Delete Data . 33Deleting data using python . 3411. Python PostgreSQL — Drop Table . 37Removing an entire table using Python. 383

Python PostgreSQL12. Python PostgreSQL – Limit . 40Limit clause using python . 4113. Python PostgreSQL ― Join . 43Joins using python . 4414. Python PostgreSQL — Cursor Object . 464

1. Python PostgreSQL ― IntroductionPython PostgreSQLPostgreSQL is a powerful, open source object-relational database system. It has more than15 years of active development phase and a proven architecture that has earned it a strongreputation for reliability, data integrity, and correctness.To communicate with PostgreSQL using Python you need to install psycopg, an adapterprovided 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 isavailable under PIP (package manager of python)Installing Psycog2 using PIPFirst of all, make sure python and PIP is installed in your system properly and, PIP is upto-date.To upgrade PIP, open command prompt and execute the following command:C:\Users\Tutorialspoint python -m pip install --upgrade pipCollecting pipUsing ling collected packages: pipFound existing installation: pip 19.0.3Uninstalling pip-19.0.3:Successfully uninstalled pip-19.0.3Successfully installed pip-19.2.2Then, open command prompt in admin mode and execute the pip install psycopg2binary command as shown below:C:\WINDOWS\system32 pip install psycopg2-binaryCollecting psycopg2-binaryUsing ed8d4cb9fc6d/psycopg2 binary-2.8.3-cp37-cp37m-win32.whlInstalling collected packages: psycopg2-binarySuccessfully installed psycopg2-binary-2.8.3VerificationTo verify the installation, create a sample python script with the following line in it.5

Python PostgreSQLimport mysql.connectorIf the installation is successful, when you execute it, you should not get any errors:D:\Python PostgreSQL import psycopg2D:\Python PostgreSQL 6

2. Python PostgreSQL — Database ConnectionPython PostgreSQLPostgreSQL provides its own shell to execute queries. To establish connection with thePostgreSQL database, make sure that you have installed it properly in your system. Openthe PostgreSQL shell prompt and pass details like Server, Database, username, andpassword. If all the details you have given are appropriate, a connection is establishedwith PostgreSQL database.While passing the details you can go with the default server, database, port and, username suggested by the shell.Establishing connection using pythonThe connection class of the psycopg2 represents/handles an instance of a connection.You can create new connections using the connect() function. This accepts the basicconnection parameters such as dbname, user, password, host, port and returns aconnection object. Using this function, you can establish a connection with the PostgreSQL.ExampleThe following Python code shows how to connect to an existing database. If the databasedoes not exist, then it will be created and finally a database object will be returned. Thename of the default database of PostgreSQL is postrgre. Therefore, we are supplying it asthe database name.import psycopg2#establishing the connectionconn psycopg2.connect(database "postgres", user 'postgres',password 'password', host '127.0.0.1', port '5432')7

Python PostgreSQL#Creating a cursor object using the cursor() methodcursor conn.cursor()#Executing an MYSQL function using the execute() methodcursor.execute("select version()")# Fetch a single row using fetchone() method.data cursor.fetchone()print("Connection established to: ",data)#Closing the connectionconn.close()Connection established to:1914, 64-bit',)('PostgreSQL 11.5, compiled by Visual C buildOutputConnection established to: ('PostgreSQL 11.5, compiled by Visual C build1914, 64-bit',)8

3. Python PostgreSQL ― Create DatabasePython PostgreSQLYou can create a database in PostgreSQL using the CREATE DATABASE statement. Youcan execute this statement in PostgreSQL shell prompt by specifying the name of thedatabase to be created after the command.SyntaxFollowing is the syntax of the CREATE DATABASE statement.CREATE DATABASE dbname;ExampleFollowing statement creates a database named testdb in PostgreSQL.postgres # CREATE DATABASE testdb;CREATE DATABASEYou can list out the database in PostgreSQL using the \l command. If you verify the list ofdatabases, you can find the newly created database as follows:postgres # \lList of databasesName 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 English United States.1252 . postgres UTF8(5 rows)You can also create a database in PostgreSQL from command prompt using the commandcreatedb, a wrapper around the SQL statement CREATE DATABASE.C:\Program Files\PostgreSQL\11\bin createdb -h localhost -p 5432 -U postgres sampledbPassword:9

Python PostgreSQLCreating a database using pythonThe cursor class of psycopg2 provides various methods execute various PostgreSQLcommands, 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 andexecutes it.Therefore, to create a database in PostgreSQL, execute the CREATE DATABASE queryusing this method.ExampleFollowing python example creates a database named mydb in PostgreSQL database.import psycopg2#establishing the connectionconn 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() methodcursor conn.cursor()#Preparing query to create a databasesql '''CREATE database mydb''';#Creating a databasecursor.execute(sql)print("Database created successfully.")#Closing the connectionconn.close()OutputDatabase created successfully.10

4. Python PostgreSQL - Create TablePython PostgreSQLYou can create a new table in a database in PostgreSQL using the CREATE TABLEstatement. While executing this you need to specify the name of the table, column namesand their data types.SyntaxFollowing is the syntax of the CREATE TABLE statement in PostgreSQL.CREATE TABLE table name(column1 datatype,column2 datatype,column3 datatype,.columnN datatype,);ExampleFollowing 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 TABLEpostgres #You can get the list of tables in a database in PostgreSQL using the \dt command. Aftercreating a table, if you can verify the list of tables you can observe the newly created tablein it as follows:postgres # \dtList of relationsSchema Name Type Owner-------- ------------ ------- ---------public cricketers table postgres11

Python PostgreSQL(1 row)postgres #In the same way, you can get the description of the created table using \d as shown below:postgres # \d cricketersTable "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 pythonTo create a table using python you need to execute the CREATE TABLE statement usingthe execute() method of the Cursor of pyscopg2.The following Python example creates a table with name employee.import psycopg2#Establishing the connectionconn psycopg2.connect(database "mydb", user 'postgres', password 'password',host '127.0.0.1', port '5432')#Creating a cursor object using the cursor() methodcursor conn.cursor()#Doping EMPLOYEE table if already exists.cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")#Creating table as per requirementsql '''CREATE TABLE EMPLOYEE(FIRST NAMELAST NAMECHAR(20) NOT NULL,CHAR(20),AGE INT,12

Python PostgreSQLSEX CHAR(1),INCOME FLOAT)'''cursor.execute(sql)print("Table created successfully.")#Closing the connectionconn.close()OutputTable created successfully.13

5. Python PostgreSQL — Insert DataPython PostgreSQLYou can insert record into an existing table in PostgreSQL using the INSERT INTOstatement. While executing this, you need to specify the name of the table, and values forthe columns in it.SyntaxFollowing 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, andvalue1, value2, value3,. are the values you need to insert into the table.ExampleAssume we have created a table with name CRICKETERS using the CREATE TABLEstatement 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 TABLEpostgres #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 1postgres #While inserting records using the INSERT INTO statement, if you skip any columns namesRecord 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 114

Python PostgreSQLYou can also insert records into a table without specifying the column names, if the orderof 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 1postgres # insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi','India');INSERT 0 1postgres # insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur','India');INSERT 0 1postgres #After inserting the records into a table you can verify its contents using the SELECTstatement as shown below:postgres # SELECT * from CRICKETERS;first name last name age place of birth country------------ ------------ ----- ---------------- ------------Shikhar Dhawan 33 DelhiJonathan Trott Kumara Sangakkara 41 Matale SrilankaVirat Kohli 30 Delhi IndiaRohit Sharma 32 Nagpur India India SouthAfrica(5 rows)Inserting data using pythonThe cursor class of psycopg2 provides a method with name execute() method. This methodaccepts 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 username, password, host (optional default: localhost) and, database (optional) asparameters to it. Turn off the auto-commit mode by setting false as value to the attributeautocommit. The cursor() method of the Connection class of the psycopg2 library returns acursor object. Create a cursor object using this method.15

Python PostgreSQL Then, execute the INSERT statement(s) by passing it/them as a parameter to theexecute() method.ExampleFollowing Python program creates a table with name EMPLOYEE in PostgreSQL databaseand inserts records into it using the execute() method:import psycopg2#Establishing the connectionconn psycopg2.connect(database "mydb", user 'postgres', password 'password',host '127.0.0.1', port '5432')#Setting auto commit falseconn.autocommit True#Creating a cursor object using the cursor() methodcursor 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 databaseconn.commit()print("Records inserted.")16

Python PostgreSQL# Closing the connectionconn.close()OutputRecords inserted.17

6. Python PostgreSQL ― Select DataPython PostgreSQLYou can retrieve the contents of an existing table in PostgreSQL using the SELECTstatement. At this statement, you need to specify the name of the table and, it returns itscontents in tabular format which is known as result set.SyntaxFollowing is the syntax of the SELECT statement in PostgreSQL:SELECT column1, column2, columnN FROM table name;ExampleAssume we have created a table with name CRICKETERS using the following query:postgres # CREATE TABLE CRICKETERS ( First Name VARCHAR(255), Last NameVARCHAR(255), Age int, Place Of Birth VARCHAR(255), Country VARCHAR(255));CREATE TABLEpostgres #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 1postgres # insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown','SouthAfrica');INSERT 0 1postgres # insert into C

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:\Users\Tutorialspoint python -m pip install --upgrade pip Collecting pip Using cached

Related Documents:

PostgreSQL Python EDB PostgreSQL EBD . Mac brew postgresql Homebrew ' macOS ' . . brew PostgreSQL . brew update brew install postgresql Homebrew . brew search postgresql brew search postgresql. PostgreSQL brew info postgresql. Homebrew . brew services start postgresql .

Taming Performance Variability in PostgreSQL Shawn S. Kim. PostgreSQL Execution Model 2 Storage Device Linux Kernel P1 Client P2 I/O P3 P4 Request Response I/O I/O I/O PostgreSQL Database . Checkpoint tuning makes PostgreSQL unpredictable Server: r5d.4xlarge, 300GB NVMe SSD, CentOS 7, PostgreSQL v11.3 (shared_buffers 32GB, effective_cache .

29. PostgreSQL – NULL Values . Streaming Replication (as of 9.0) Hot Standby (as of 9.0) . This chapter explains about installing the PostgreSQL on Linux, Windows and Mac OS platforms. Installing PostgreSQL on Linux/Unix Follow the given steps to install PostgreSQL on your Linux machine. Make sure you are logged

Python Programming for the Absolute Beginner Second Edition. CONTENTS CHAPTER 1 GETTING STARTED: THE GAME OVER PROGRAM 1 Examining the Game Over Program 2 Introducing Python 3 Python Is Easy to Use 3 Python Is Powerful 3 Python Is Object Oriented 4 Python Is a "Glue" Language 4 Python Runs Everywhere 4 Python Has a Strong Community 4 Python Is Free and Open Source 5 Setting Up Python on .

Python 2 versus Python 3 - the great debate Installing Python Setting up the Python interpreter About virtualenv Your first virtual environment Your friend, the console How you can run a Python program Running Python scripts Running the Python interactive shell Running Python as a service Running Python as a GUI application How is Python code .

add apt repository ppa:pitti/postgresql apt get update apt get install postgresql 9.1 postgresql server dev 9.1 libpq dev python python dev python

Python is readable 5 Python is complete—"batteries included" 6 Python is cross-platform 6 Python is free 6 1.3 What Python doesn't do as well 7 Python is not the fastest language 7 Python doesn't have the most libraries 8 Python doesn't check variable types at compile time 8 1.4 Why learn Python 3? 8 1.5 Summary 9

Poor strategic asset management decisions may result in wasted resources and other, often longer term, inefficiencies. Strategic asset management can be a creative process and the best social landlords will be focussed on exactly the most important asset management issues. Good strategic asset management is core business for governing bodies of Registered Social Landlords (RSLs) and council .