# Install the required packages
# conda install psycopg2
# Import the required library
import psycopg2
# Connect to the database
= psycopg2.connect(
conn ="localhost",
host=5432,
port="tutorial",
database="postgres",
user="") # Change the password to your password
password
# Create a cursor object. This is used to interact with the database.
= conn.cursor() cur
PostgreSQL Example
Here is an example of how to use PostgreSQL with Python. The example uses the psycopg2
library to connect to a PostgreSQL database and perform some basic operations. Please refer to tutorial 04 for more information.
# Now we will execute the queries
"SELECT * FROM students")
cur.execute(
# Fetch the results
= cur.fetchall()
rows
# Print the results
for row in rows:
print(row)
(1, 'Alice', 25)
(2, 'Bob', 30)
(3, 'Charlie', 35)
# Let us run another query
"SELECT * FROM students WHERE name = 'Bob' ")
cur.execute(
# Fetch the results
= cur.fetchall()
rows
# Print the results
for row in rows:
print(row)
(2, 'Bob', 30)
# Close the cursor and the connection. This is important as it will free up the resources.
cur.close()
# Commit the transaction. This will save the changes to the database.
# You do not need to do this if you are only reading from the database.
conn.commit()
# Close the connection
conn.close()
# Converting tables to pandas dataframe
# Install the required packages
# conda install pandas sqlalchemy
# Import the required libraries
import pandas as pd
from sqlalchemy import create_engine
# Create a connection to the database
# The syntax is 'postgresql://username:password@host:port/database'
# In our case, the username is postgres, password is blank, host is localhost,
# port is 5432 and database is tutorial
= create_engine('postgresql://postgres:@localhost:5432/tutorial')
engine
# Read the table into a pandas dataframe
= pd.read_sql_table('students', engine)
df
# Print the dataframe
print(df)
id name age
0 1 Alice 25
1 2 Bob 30
2 3 Charlie 35