def updateTable(mobileId, price):
try:
connection = db_conn()
cursor = connection.cursor()
print("Table Before updating record ")
sql_select_query = """select * from mobile where id = %s"""
cursor.execute(sql_select_query, (mobileId, ))
record = cursor.fetchone()
print(record)
# Update single record now
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.execute(sql_update_query, (price, mobileId))
connection.commit()
count = cursor.rowcount
print(count, "Record Updated successfully ")
print("Table After updating record ")
sql_select_query = """select * from mobile where id = %s"""
cursor.execute(sql_select_query, (mobileId, ))
record = cursor.fetchone()
print(record)
print('type of record: ', type(record))
except (Exception, psycopg2.Error) as error:
print("Error in update operation", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def getMobileDetails(mobileID):
try:
connection = db_conn()
print("Using Python variable in PostgreSQL select Query")
cursor = connection.cursor()
postgreSQL_select_Query = "select * from mobile where id = %s"
cursor.execute(postgreSQL_select_Query, (mobileID, ))
mobile_records = cursor.fetchall()
for row in mobile_records:
print(
"Id = ",
row[0],
)
print("Model = ", row[1])
print("Price = ", row[2])
except (Exception, psycopg2.Error) as error:
print("Error fetching data from PostgreSQL table", error)
finally:
# closing database connection
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed \n")
def deleteInBulk(records):
try:
connection = db_conn()
cursor = connection.cursor()
ps_delete_query = """Delete from mobile where id = %s"""
cursor.executemany(ps_delete_query, records)
connection.commit()
row_count = cursor.rowcount
print(row_count, "Record Deleted")
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def deleteData(mobileId):
try:
connection = db_conn()
cursor = connection.cursor()
# Update single record now
sql_delete_query = """Delete from mobile where id = %s"""
cursor.execute(sql_delete_query, (mobileId, ))
connection.commit()
count = cursor.rowcount
print(count, "Record deleted successfully ")
except (Exception, psycopg2.Error) as error:
print("Error in Delete operation", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def bulkInsert(records):
try:
connection = db_conn()
cursor = connection.cursor()
sql_insert_query = """ INSERT INTO mobile (id, model, price)
VALUES (%s,%s,%s) """
# executemany() to insert multiple rows rows, execute() only insert one row
result = cursor.executemany(sql_insert_query, records)
connection.commit()
print(cursor.rowcount,
"Record inserted successfully into mobile table")
except (Exception, psycopg2.Error) as error:
print("Failed inserting record into mobile table {}".format(error))
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def updateInBulk(records):
try:
connection = db_conn()
cursor = connection.cursor()
# Update multiple records
sql_update_query = """Update mobile set price = %s where id = %s"""
cursor.executemany(sql_update_query, records)
connection.commit()
# Use cursor.rowcount to get the total number of rows affected by the executemany() method
row_count = cursor.rowcount
print(row_count, "Records Updated")
except (Exception, psycopg2.Error) as error:
print("Error while updating PostgreSQL table", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def get_doctors(hospital_id):
try:
connection = db_conn()
print("Using Python variable in PostgreSQL select Query")
cursor = connection.cursor()
postgreSQL_select_Query = "select * from doctor inner join hospital on doctor.Hospital_Id = hospital.Hospital_Id where doctor.Hospital_Id = %s"
cursor.execute(postgreSQL_select_Query, (hospital_id, ))
records = cursor.fetchall()
for row in records:
print(row)
except (Exception, psycopg2.Error) as error:
print("Error fetching data from PostgreSQL table", error)
finally:
# closing database connection
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed \n")
import psycopg2
from psycopg2 import Error
from conn import db_conn
try:
connection = db_conn()
cursor = connection.cursor()
"""
We used a parameterized query to pass parameter values at execution time.
In the end, we make our changes persistent into the database using the cursor.commit method.
Using a parameterized query we can pass python variables as a query parameter in which we use placeholders (%s) for parameters
https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/#What_is_parameterized_query_in_python
"""
postgres_insert_query = """ INSERT INTO mobile (ID, MODEL, PRICE) VALUES (%s,%s,%s)"""
record_to_insert = (5, 'One Plus 6', 950)
cursor.execute(postgres_insert_query, record_to_insert)
connection.commit()
count = cursor.rowcount
print(count, "Record inserted successfully into mobile table")
except (Exception, Error) as error:
if(connection):
print("Failed to insert record into mobile table", error)
finally:
# closing database connection.
if(connection):
