Introduction
PostgreSQL is one of the most advanced and widely used relational database management systems. It's extremely popular for many reasons, a few of which include it being open source, its extensibility, and its ability to handle many different types of applications and varying loads.
With Python, you can easily establish a connection to your PostgreSQL database. There are many Python drivers for PostgreSQL, with "psycopg" being the most popular one. Its current version is psycopg2.
In this article, we'll be discussing how to access a PostgreSQL database in Python using the psycopg2 driver.
The psycopg2 Module
We can integrate Postgres with Python using the psycopg2 module. psycopg2 is a Postgres database adapter for Python. To use this module, you should first install it. This can be done using the pip
command, as shown below:
$ pip3 install psycopg2
Note that I am using Python 3.5, hence I have used pip3
instead of pip
.
Once the module has been installed, you can use it to connect to your database in your application.
Connecting to a Database
To connect to your database, you should first create a connection object representing the database. Next, you should create a cursor object to help you in execution of your SQL statements.
The following example demonstrates how to establish a connection to the database named "postgres":
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")
print("Database opened successfully")
Output:
Database opened successfully
The following is the list of parameters that have been passed to the connect()
method:
database
: The name of the database you need to connect to.user
: The username to be used for authentication.password
: The database password for the user.host
: The address of the database server. For example, a domain name, "localhost", or an IP address.port
: The port number. If you don't provide this, the default one will be used, which is 5432.
Note that the values for the above parameters should be correct for the connection to be successful. If not, an exception will be generated. The output in the above code shows that the connection to the database has been established successfully.
Creating a Table
To create a Postgres table in Python, we use the CREATE TABLE
SQL statement. This query should be executed after establishing a connection to the database. We also create a cursor object by calling the cursor()
method that belongs to the connection
object. This cursor
object is used to actually execute your commands.
We then call the execute()
method of the cursor
object to help us in creating the table. Finally, we need to commit and close the connection. "Committing" the connection tells the driver to send the commands to the database.
The following example demonstrates this:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute('''CREATE TABLE STUDENT
(ADMISSION INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
COURSE CHAR(50),
DEPARTMENT CHAR(50));''')
print("Table created successfully")
con.commit()
con.close()
Output
Database opened successfully
Table created successfully
The commit()
method helps us apply the changes that we have made to the database, and these changes cannot be undone if commit()
runs successfully. The close()
method will close the connection to the database.
At this point we have created a table with 4 columns, all of which have various data types. The above output shows that the table was created successfully.
Inserting Data
We can insert either a single record or multiple records into a Postgres database table. Again, we must first establish a connection to the database server by calling the connect()
function. Next, we should create a cursor object by calling the cursor()
method. Lastly, we should run the INSERT
statement via the execute()
method to add the data into the table.
Here is an example of this in action:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, 'John', 18, 'Computer Science', 'ICT')");
con.commit()
print("Record inserted successfully")
con.close()
Output
Database opened successfully
Record inserted successfully
After running this code we have inserted a single record into our database table. This was done by specifying the name of the table as well as the columns into which we need to insert the data into. It is also possible for us to insert multiple records with one command. For example:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3419, 'Abel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3421, 'Joel', 17, 'Computer Science', 'ICT')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3422, 'Antony', 19, 'Electrical Engineering', 'Engineering')");
cur.execute("INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3423, 'Alice', 18, 'Information Technology', 'ICT')");
con.commit()
print("Records inserted successfully")
con.close()
Output
Database opened successfully
Records inserted successfully
Since the commit()
method isn't called until after we "execute" all of the INSERT
statements, multiple records are inserted with a single call of the commit()
method.
Retrieving Data
You can select data from a Postgres database and view the table records. First, you must establish a connection to the database using the connect()
function. Next, a new cursor should be created by calling the cursor()
method. The created cursor object can then be used to execute the SELECT
statement for querying data from the database.
For example:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
print("ADMISSION =", row[0])
print("NAME =", row[1])
print("AGE =", row[2])
print("COURSE =", row[3])
print("DEPARTMENT =", row[4], "\n")
print("Operation done successfully")
con.close()
Output
Database opened successfully
ADMISSION = 3420
NAME = John
AGE = 18
COURSE = Computer Science
DEPARTMENT = ICT
ADMISSION = 3419
NAME = Abel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT
ADMISSION = 3421
NAME = Joel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT
ADMISSION = 3422
NAME = Antony
AGE = 19
COURSE = Electrical Engineering
DEPARTMENT = Engineering
ADMISSION = 3423
NAME = Alice
AGE = 18
COURSE = Information Technology
DEPARTMENT = ICT
Operation done successfully
Here we have retrieved data from the database by specifying the table and names of the columns that we need to retrieve from the database table. This data is returned to us as list of tuples, with the "top level" list being the rows of data. Then each row is a tuple of the column data. If no rows are returned for the query then an empty list is returned by fetchall()
.
Updating Tables
We can update or modify the details of a record that has already been inserted into a database table. First, we have to establish a connection to the database using the connect()
method. Next, we call the cursor()
function to create a cursor object. Finally, we run the execute()
method to execute the UPDATE
statement with input values.
For example:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("UPDATE STUDENT set AGE = 20 where ADMISSION = 3420")
con.commit()
print("Total updated rows:", cur.rowcount)
cur.execute("SELECT admission, age, name, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
print("ADMISSION =", row[0])
print("NAME =", row[1])
print("AGE =", row[2])
print("COURSE =", row[2])
print("DEPARTMENT =", row[3], "\n")
print("Operation done successfully")
con.close()
Output
Database opened successfully
Total updated rows: 1
ADMISSION = 3419
NAME = 17
AGE = Abel
COURSE = Abel
DEPARTMENT = Computer Science
ADMISSION = 3421
NAME = 17
AGE = Joel
COURSE = Joel
DEPARTMENT = Computer Science
ADMISSION = 3422
NAME = 19
AGE = Antony
COURSE = Antony
DEPARTMENT = Electrical Engineering
ADMISSION = 3423
NAME = 18
AGE = Alice
COURSE = Alice
DEPARTMENT = Information Technology
ADMISSION = 3420
NAME = 20
AGE = John
COURSE = John
DEPARTMENT = Computer Science
Operation done successfully
In the above eample, we have updated the value of column AGE
for all rows where the ADMISSION
is 3420. After executing the update we then retrieve this data to verify that the appropriate rows/columns have been updated.
Deleting Rows
To delete a record from a Postgres database table, we should first establish a connection to the database server. Secondly, a cursor object should be created by calling the cursor()
function. We then run the DELETE
statement to perform the deletion.
For example:
import psycopg2
con = psycopg2.connect(database="postgres", user="postgres", password="Kaliakakya", host="127.0.0.1", port="5432")
print("Database opened successfully")
cur = con.cursor()
cur.execute("DELETE from STUDENT where ADMISSION=3420;")
con.commit()
print("Total deleted rows:", cur.rowcount)
cur.execute("SELECT admission, name, age, course, department from STUDENT")
rows = cur.fetchall()
for row in rows:
print("ADMISSION =", row[0])
print("NAME =", row[1])
print("AGE =", row[2])
print("COURSE =", row[3])
print("DEPARTMENT =", row[4], "\n")
print("Deletion successful")
con.close()
Output
Database opened successfully
Total deleted rows: 1
ADMISSION = 3419
NAME = Abel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT
ADMISSION = 3421
NAME = Joel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT
ADMISSION = 3422
NAME = Antony
AGE = 19
COURSE = Electrical Engineering
DEPARTMENT = Engineering
ADMISSION = 3423
NAME = Alice
AGE = 18
COURSE = Information Technology
DEPARTMENT = ICT
Deletion successful
In this example we have deleted all records where the admission number of the student is 3420, which in this case is only one row. After retrieving the data using SELECT
, we can see that this record is not part of the above output, confirming that it has been deleted from the database.
Conclusion
With Python, there are various ways we can access a PostgreSQL database. There are many database drivers for Python that we can use for this purpose, but psycopg is the most popular one. In this article we showed how to install the module, establish a connection to your PostgreSQL database, and execute common SQL queries using Python code.
from Planet Python
via read more
No comments:
Post a Comment