def fetch_all_person_rows():
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
SQL = 'SELECT * FROM person;'
cursor.execute(SQL)
row = cursor.fetchall()
print(row)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def describe_column():
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
SQL = "SELECT column_name FROM information_schema.columns WHERE table_name = 'person';"
cursor.execute(SQL)
row = cursor.fetchall()
print(row)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def delete_certificate(iidee):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
'''
DELETE FROM certificates WHERE id = %s;
''', (iidee, ))
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def insert_certificate_row(name, personid):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
'''
INSERT INTO certificates(name,person_id)
VALUES (%s, %s);
''', (name, personid))
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def get_certificates():
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
q2 = "SELECT * FROM certificates"
cursor.execute(q2)
row2 = cursor.fetchall()
description = cursor.description
print(description)
print(row2)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def update_certificate(iidee, name, personid):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
'''
UPDATE certificates SET (name,person_id) = (%s, %s)
WHERE id = %s;
''', (name, personid, iidee))
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def update_person(iidee, name, age, student=False):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
'''
UPDATE person SET (name,age,student) = (%s, %s, %s)
WHERE id = %s;
''', (name, age, student, iidee))
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def insert_person_row(nimi, ikä, student=False):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
'''
INSERT INTO person(name,age,student)
VALUES (%s, %s, %s);
''', (nimi, ikä, student))
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def check_cert_userid(userinput):
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute(
"""
SELECT COUNT(id) FROM person WHERE id = %s ;
""", (userinput, ))
row = cursor.fetchone()
cursor.close()
return row
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def aws_holders():
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
SQL = '''
SELECT person.name,certificates.name
FROM person,certificates
WHERE certificates.name = 'AWS Certified Cloud Associate '
AND certificates.person_id=person.id;
'''
cursor.execute(SQL)
row = cursor.fetchall()
print(row)
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
def create_table():
con = None
try:
con = psycopg2.connect(**config())
cursor = con.cursor()
cursor.execute('''
CREATE TABLE pets (
id SERIAL PRIMARY KEY,
name varchar(255) NOT NULL,
animal varchar(255) NOT NULL,
owner_id int,
CONSTRAINT fk_owner
FOREIGN KEY(owner_id)
REFERENCES person(id)
) ;
''')
con.commit()
cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if con is not None:
con.close()
