def fill_database(sqlfile='database_interactions/huwebshop.sql'):
"""
Fill the database with the database structure from a sql file.
:param sqlfile:
:return:
"""
db = config()
con = psycopg2.connect(**db)
cursor = con.cursor()
# Open the given sql file
with open(sqlfile, 'r') as file:
content_list = [line.rstrip(';') for line in file]
tempLst = []
for i in content_list:
if i[:2] != '--' and i != '\n':
tempLst.append(i[:-1])
content_list = tempLst
separator = ' '
content = separator.join(content_list)
content_list = content.split(';')
tempLst = []
for i in content_list:
tempLst.append(i + ';')
tempLst.remove(tempLst[-1])
content_list = tempLst
for i in content_list:
cursor.execute(i)
con.commit()
con.close()
def drop_database(dbname):
# Configure parser for database.ini
parser = ConfigParser()
parser.read('database_interactions/database.ini')
# If Database line exists remove it otherwise pass
try:
parser.remove_option('postgresql', 'database')
with open('database.ini', 'w') as configFile:
parser.write(configFile)
except ValueError:
print(ValueError)
pass
try:
# Use config functie to get values from database.ini
db = config()
con = psycopg2.connect(**db)
cursor = con.cursor()
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
drop_table_command = f"DROP DATABASE {dbname};"
cursor.execute(drop_table_command)
con.commit()
print('Database has been dropped')
con.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
def connect_to_db():
try:
# Use config functie to get values from database.ini
db = config()
con = psycopg2.connect(**db)
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
exit()
return con
def create_database(dbname='huwebshop'):
"""
Function to create a new database and extend the database.ini file.
:param dbname:
:return:
"""
# Configure parser for database.ini
parser = ConfigParser()
parser.read('database_interactions/database.ini')
# If Database line exists remove it otherwise pass
try:
parser.remove_option('postgresql', 'database')
with open('database.ini', 'w') as configFile:
parser.write(configFile)
except ValueError:
pass
try:
# Use config functie to get values from database.ini
db = config()
con = psycopg2.connect(**db)
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = con.cursor()
sqlCreateDatabase = "create database " + dbname + ";"
cursor.execute(sqlCreateDatabase)
con.commit()
con.close()
print('Database created')
except (Exception, psycopg2.DatabaseError) as error:
print(error)
# Add database name to the database.ini file
parser['postgresql']['database'] = dbname
with open('database.ini', 'w') as configFile:
parser.write(configFile)
print(f'{dbname} aangevuld aan de database.ini file')
def __init__(self):
db = config()
self.con = psycopg2.connect(**db)
def open_connection(self):
db = config()
con = psycopg2.connect(**db)
return con