Passing parameters to SQL queries
Python variables are converted to SQL values with Psycopg, Python determines the function used to convert the object into a string representation suitable for PostgreSQL.Passing parameters to an SQL statement happens in functions such as cursor.execute() by using %s as the placeholder into the SQL statement.
Example 4: Fetching the data from the database and displaying it into the terminal.
Python3
from mysqlx import Rowimport psycopg2DB_NAME = "tkgafrwp"DB_USER = "tkgafrwp"DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"DB_HOST = "tyke.db.elephantsql.com"DB_PORT = "5432"conn = psycopg2.connect(database=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT)print("Database connected successfully")cur = conn.cursor()cur.execute("SELECT * FROM Employee")rows = cur.fetchall()for data in rows: print("ID :" + str(data[0])) print("NAME :" + data[1]) print("EMAIL :" + data[2])print('Data fetched successfully')conn.close() |
Example 5: Updating the data in the database.
Python3
from turtle import stfrom mysqlx import Rowimport psycopg2DB_NAME = "tkgafrwp"DB_USER = "tkgafrwp"DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"DB_HOST = "tyke.db.elephantsql.com"DB_PORT = "5432"conn = psycopg2.connect(database=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT)print("Database connected successfully")cur = conn.cursor()cur.execute("UPDATE Employee set EMAI = 'updated@gmail.com' WHERE ID =1 ")conn.commit()print("Data updated Successfully")print("Total row affected "+str(cur.rowcount))conn.close() |
Example 6: Deleting data from the database.
Python3
from turtle import stfrom mysqlx import Rowimport psycopg2DB_NAME = "tkgafrwp"DB_USER = "tkgafrwp"DB_PASS = "iYYtLAXVbid-i6MV3NO1EnU-_9SW2uEi"DB_HOST = "tyke.db.elephantsql.com"DB_PORT = "5432"conn = psycopg2.connect(database=DB_NAME,user=DB_USER,password=DB_PASS, host=DB_HOST,port=DB_PORT)print("Database connected successfully")cur = conn.cursor()cur.execute("DELETE FROM Employee WHERE ID =1 ")conn.commit()print("Data deleted Successfully")print("Total row affected "+str(cur.rowcount))conn.close() |
The main entry points of Psycopg are:
- The connect() function creates a new database session and returns a new instance of connection.
- The class connection encloses a database session. It allows to :
- create new cursor instance
- terminate transaction using commit() or rollback() methods.
- The cursor allows interaction with the database:
- send commands to the database using execute() and executemany() methods.
- retrieve data from the database using methods such as fetchone(), fetchmany(), fetchall(), or by iteration.
Introduction to Psycopg2 module in Python
Psycopg is the most popular PostgreSQL adapter used in Python. Its works on the principle of the whole implementation of Python DB API 2.0 along with the thread safety (the same connection is shared by multiple threads). It is designed to perform heavily multi-threaded applications that usually create and destroy lots of cursors and make a large number of simultaneous INSERTS or UPDATES. Psycopg features client-side and server-side cursors, asynchronous communication, and notification. Psycopg 2 is both Unicode and Python 3 friendly.