Python Getting started with psycopg2-PostgreSQL

In this tutorial, we are going to learn how to use PostgreSQL with Python. You have to install certain thing before going into the tutorial. Let's install them.

Install the PostgreSQL with the guide..

Install the Python module psycopg2 for PostgreSQL connection and working. Run the command to install it.

pip install psycopg2

Now, open the pgAdmin. And create a sample database. Next, follow the below steps to get started with database operations.

• Import the psycopg2 module.
• Store the database name, username, and password in separate variables.
• Make a connection to the database using psycopg2.connect(database=name, user=name, password=password) method.
• Instantiate a cursor object to execute SQL commands.
• Create queries and execute them with cursor.execute(query) method.
• And get the information using cursor.fetchall() method if available.
• Close the connection using connection.close() method.

Example

# importing the psycopg2 module
import psycopg2
# storing all the information
database = 'testing'
user = 'postgres'
password = 'C&o%Z?bc'
# connecting to the database
connection = psycopg2.connect(database=database, user=user, password=password)
# instantiating the cursor
cursor = connection.cursor()
# query to create a table
create_table = "CREATE TABLE testing_members (id SERIAL PRIMARY KEY, name VARCH
25) NOT NULL)"
# executing the query
cursor.execute(create_table)
# sample data to populate the database table
testing_members = ['Python', 'C', 'JavaScript', 'React', 'Django']
# query to populate the table testing_members
for testing_member in testing_members:
   populate_db = f"INSERT INTO testing_members (name) VALUES ('{testing_member
   cursor.execute(populate_db)
   # saving the changes to the database
   connection.commit()
   # query to fetch all
   fetch_all = "SELECT * FROM testing_members"
   cursor.execute(fetch_all)
   # fetching all the rows
   rows = cursor.fetchall()
   # printing the data
   for row in rows:
      print(f"{row[0]} {row[1]}")
      # closing the connection
      connection.close()

Output

If you run the above code, then you will get the following result.

1 Python
2 C
3 JavaScript
4 React
5 Django

Conclusion

If you have any doubts in the tutorial, mention them in the comment section.

Hafeezul Kareem

Published on 07-Jul-2020 12:25:59