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):