def insert_vendor(vendor_name):
""" insert a new vendor into the vendors table """
sql = """INSERT INTO vendors(vendor_name)
VALUES(%s) RETURNING vendor_id;"""
conn = None
vendor_id = None
try:
# read database configuration
params = config.config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.execute(sql, (vendor_name, ))
# get the generated id back
vendor_id = cur.fetchone()[0]
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return vendor_id
def update_sql(name_table, name_parameter, chat_id, parameter):
""" update vendor name based on the vendor id """
print("update")
sql = """ UPDATE {}
SET {} = %s
WHERE chat_id = %s""".format(name_table, name_parameter)
conn = None
updated_rows = 0
try:
# read database configuration
params = config.config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the UPDATE statement
cur.execute(sql, (str(parameter), chat_id))
# get the number of updated rows
updated_rows = cur.rowcount
# Commit the changes to the database
conn.commit()
# Close communication with the PostgreSQL database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
return updated_rows
def insert_sql(table_name, answers):
""" insert a new vendor into the vendors table """
n_values = ['%s'] * len(answers)
n_values = ', '.join(map(str, n_values))
name_columns = ''
sql = """INSERT INTO {0} ({1})
VALUES ({2})""".format(table_name, name_columns, n_values)
conn = None
try:
# read database configuration
params = config.config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.execute(sql, answers)
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def get_parts(retrieve_parameters, title_table):
""" query parts from the parts table """
conn = None
try:
params = config.config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute("SELECT {} FROM {}".format(', '.join(retrieve_parameters),
title_table))
rows = cur.fetchall()
rows_copy = []
for row in rows:
rows_copy.append(row)
cur.close()
return rows_copy
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def create_tables():
""" create tables in the PostgreSQL database"""
conn = None
try:
# read the connection parameters
params = config.config()
# connect to the PostgreSQL server
conn = psycopg2.connect(**params)
cur = conn.cursor()
# create table one by one
for command in commands:
cur.execute(command)
# close communication with the PostgreSQL database server
cur.close()
# commit the changes
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
def insert_vendor_list(vendor_list):
""" insert multiple vendors into the vendors table """
sql = "INSERT INTO vendors(vendor_name) VALUES(%s)"
conn = None
try:
# read database configuration
params = config.config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
cur.executemany(sql, vendor_list)
# commit the changes to the database
conn.commit()
# close communication with the database
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()