Performing Create operation in PostgreSQL database using Python

Establishing a connection with the PostgreSQL database

Performing Insert operation in PostgreSQL database using Python

The syntax for creating a table is similar to that of the syntax used in conventionally SQL statements. Create different tables with required columns and column constraints as shown. Establish a connection with the PostgreSQL using connect() as discussed above
Now, instantiate a new cursor() object. A cursor is a read-only pointer that allows a program, to access the result set of a query.
Drop any tables in the same name if already exists. Then, pass the query for creating a table to the execute() function of psycopg2.
Finally call the commit() method, indicating the connector object which was created to establish a connection with the database to commit all changes to the database. This will ensure that the tables are created.

Python3

import psycopg2
 
# Establishing the connection
conn = psycopg2.connect(
    database="databasename",
    user='username',
    password='password',
    host='hostname',
    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 publisher")
 
# Creating table as per requirement
sql = '''CREATE TABLE PUBLISHER(
                publisher_id SERIAL PRIMARY KEY,
                publisher_name VARCHAR(255) NOT NULL,
                publisher_estd INT,
                publsiher_location VARCHAR(255),
                publsiher_type VARCHAR(255)
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
 
# Closing the connection
conn.close()

Output:

Table created successfully........

Creating a table in PostgreSQL using Python

Perform PostgreSQL CRUD operations from Python

The DDL is comprised of the Create, Read, Update, Delete (CRUD) operations which form the backbone of any SQL database system. Let us discuss how to perform CRUD operations on a PostgreSQL database using python. Pyscopg2 is the most preferred mode and is widely used to connect the PostgreSQL database using python. Pyscopg2 is a Database API which is a PostgreSQL compatible driver that manages connection pools.

In this article, we will learn how to connect to the PostgreSQL database using SQLAlchemy in python and also understood the procedure on how to perform CRUD operations against the PostgreSQL database.

Performing Create operation in PostgreSQL database using Python

Python3

Perform PostgreSQL CRUD operations from Python

Similar Reads

Contact Us