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()
# 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 connect():
""" Connect to the PostgreSQL database server """
conn = None
try:
# read connection parameters
params = config()
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
conn = psycopg2.connect(**params)
#conn = psycopg2.connect(host="localhost",database="suppliers", user="******", password="******")
# create a cursor
cur = conn.cursor()
# execute a statement
print('PostgreSQL database version:')
cur.execute('SELECT version()')
# display the PostgreSQL database server version
db_version = cur.fetchone()
print(db_version)
# close the communication with the PostgreSQL
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
print('Database connection closed.')
def insertdata(datastream):
""" insert multiple vendors into the vendors table """
sql1 = "INSERT INTO currency(cur_id, cur_name, cur_sym, web_slg, rank, last_updated) VALUES(%s, %s, %s, %s, %s, %s)"
sql2 = "INSERT INTO supply(cur_id, circulating_supply, total_supply, max_supply) VALUES(%s, %s, %s, %s)"
sql3 = "INSERT INTO quotes(quote_id,cur_id, quote_name) VALUES(%s, %s, %s)"
sql4 = "INSERT INTO quote_info(quote_id, cur_id, price, volume, market_cap, percent_change_1h, percent_change_24h, percent_change_7d) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
conn = None
sqdata1 = []
sqdata2 = []
sqdata3 = []
sqdata4 = []
l = len(datastream)
print(datastream)
for i in range(l):
# 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
#[ids,cur,sym,web,rn,csup,tsup,msup,'USD',prc,vol,mar,per1h,per24h,per7d,lu]
sqdata1.append((datastream[i][0], datastream[i][1], datastream[i][2],
datastream[i][3], datastream[i][4], datastream[i][15]))
sqdata2.append((datastream[i][0], datastream[i][5], datastream[i][6],
datastream[i][7]))
sqdata3.append((i, datastream[i][0], datastream[i][8]))
sqdata4.append(
(i, datastream[i][0], datastream[i][9], datastream[i][10],
datastream[i][11], datastream[i][12], datastream[i][13],
datastream[i][14]))
sqdata1 = tuple(sqdata1)
sqdata2 = tuple(sqdata2)
sqdata3 = tuple(sqdata3)
sqdata4 = tuple(sqdata4)
try:
# read database configuration
params = config()
# connect to the PostgreSQL database
conn = psycopg2.connect(**params)
# create a new cursor
cur = conn.cursor()
# execute the INSERT statement
print("1", sqdata1, sqdata2, sqdata3, sqdata4)
cur.executemany(sql1, sqdata1)
print("2")
cur.executemany(sql2, sqdata2)
print("3")
cur.executemany(sql3, sqdata3)
print("4")
cur.executemany(sql4, sqdata4)
# 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 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()
# 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()
def create_tables():
""" create tables in the PostgreSQL database"""
commands = ("""
CREATE TABLE currency (
cur_id INTEGER PRIMARY KEY,
cur_name VARCHAR(255) NOT NULL,
cur_sym VARCHAR(255) NOT NULL,
web_slg VARCHAR(255) NOT NULL,
rank INTEGER NOT NULL,
last_updated INTEGER NOT NULL
)
""", """ CREATE TABLE supply (
cur_id INTEGER PRIMARY KEY,
circulating_supply real NOT NULL,
total_supply real NOT NULL,
max_supply real NOT NULL,
FOREIGN KEY (cur_id)
REFERENCES currency (cur_id)
ON UPDATE CASCADE ON DELETE CASCADE
)
""", """
CREATE TABLE quotes (
quote_id SERIAL NOT NULL,
cur_id INTEGER NOT NULL,
quote_name VARCHAR(255) NOT NULL,
PRIMARY KEY (cur_id, quote_id),
FOREIGN KEY (cur_id)
REFERENCES currency (cur_id)
ON UPDATE CASCADE ON DELETE CASCADE
)
""", """
CREATE TABLE quote_info (
quote_id INTEGER NOT NULL,
cur_id INTEGER NOT NULL,
price real NOT NULL,
volume real NOT NULL,
market_cap real NOT NULL,
percent_change_1h real NOT NULL,
percent_change_24h real NOT NULL,
percent_change_7d real NOT NULL,
PRIMARY KEY (cur_id , quote_id),
FOREIGN KEY (cur_id, quote_id)
REFERENCES quotes (cur_id, quote_id)
ON UPDATE CASCADE ON DELETE CASCADE
)
""")
conn = None
try:
# read the connection parameters
params = 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()