def __init__(self, order, fields):
''' Set-up class with an OrderClass and SQL Table-fields.
Will raise an exception when proper types are not provided.
'''
assert (isinstance(order, OrderClass))
self.order = order
self.fields = Norm.NormCols(fields)
self.level = CodeLevel()
class SqliteCrud:
''' Translates OrderClass + Fields into python source code. No files are created. '''
def __init__(self, order, fields):
''' Set-up class with an OrderClass and SQL Table-fields.
Will raise an exception when proper types are not provided.
'''
assert (isinstance(order, OrderClass))
self.order = order
self.fields = Norm.NormCols(fields)
self.level = CodeLevel()
def code_class_template(self, text_file):
''' Translate a local order into Python code. Returns created source-code. '''
import time
from SqltDAO.CodeGen01.Meta import Meta
self.level.set(0)
result = self.level.print("#!/usr/bin/env python3\n")
result += self.level.print("'''")
result += self.level.print("Generated by {}".format(Meta.Title()))
result += self.level.print("Generated @ {}".format(time.asctime()))
result += self.level.print("'''")
result += self.level.print("\nfrom collections import OrderedDict\n")
result += self.level.print("import sqlite3\n\n\nclass " +
self.order.class_name + ":")
result += self.level.print("")
self.level.inc()
result += self.level.print("def __init__(self):")
self.level.inc()
result += self.level.print("self.db = '" +
Norm.NormPath(self.order.db_name) + "'")
result += self.level.print("self.conn = None")
result += self.level.print("self.curs = None")
result += self.level.print("self.bOpen = False")
result += self.level.print("self.fields = " + str(self.fields))
result += self.level.print("self.table_name = '" +
self.order.table_name + "'")
result += self.level.print("")
self.level.dec()
result += self.level.print("@classmethod")
result += self.level.print("def get_fields(cls, value):")
self.level.inc()
result += self.level.print("if isinstance(value, cls):")
self.level.inc()
result += self.level.print("return list(value.fields.values())[1:]")
self.level.dec()
result += self.level.print("return value")
result += self.level.print("")
self.level.dec()
result += self.level.print("def open(self):")
self.level.inc()
result += self.level.print("if self.bOpen is False:")
self.level.inc()
result += self.level.print("self.conn = sqlite3.connect(self.db)")
result += self.level.print("self.conn.row_factory = sqlite3.Row")
result += self.level.print("self.curs = self.conn.cursor()")
result += self.level.print("self.bOpen = True")
self.level.dec()
result += self.level.print("return True")
result += self.level.print("")
self.level.dec()
result += self.level.print("def close(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print("self.conn.commit()")
result += self.level.print("self.bOpen = False")
self.level.dec()
result += self.level.print("return True")
result += self.level.print("")
self.level.dec()
result += self.level.print("def count(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print(
'res = self.curs.execute("SELECT count(*) FROM ' +
self.order.table_name + ';")')
result += self.level.print("return res.fetchone()[0]")
self.level.dec()
result += self.level.print("return -1")
result += self.level.print("")
self.level.dec()
result += self.level.print("def drop_table(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute("DrOp TaBLe IF EXISTS ' +
self.order.table_name + ';")')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def create_table(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute("' +
self.sql_create_table() + '")')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def insert(self, fields):")
self.level.inc()
result += self.level.print("fields = " + self.order.class_name +
".get_fields(fields)")
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute("' +
self.sql_insert_row() + '", fields)')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def update(self, id_, fields):")
self.level.inc()
result += self.level.print("fields = " + self.order.class_name +
".get_fields(fields)")
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute("' +
self.sql_update_row('id_', "fields"))
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def delete(self, primary_key):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute("DELETE from ' +
self.order.table_name +
' WHERE ID = ?;", [primary_key])')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def select(self, sql_select):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc()
result += self.level.print('self.curs.execute(sql_select)')
result += self.level.print("zlist = self.curs.fetchall()")
result += self.level.print("for ref in zlist:")
self.level.inc()
result += self.level.print("try:")
self.level.inc()
result += self.level.print("yield ref")
self.level.dec()
result += self.level.print("except:")
self.level.inc()
result += self.level.print("pass")
self.level.dec()
self.level.dec()
result += self.level.print("return None")
result += self.level.print("")
self.level.dec()
self.level.push()
result += self.level.print("@staticmethod")
if self.order.encoding:
encoding = "'" + self.order.encoding + "'"
else:
encoding = str(self.order.encoding)
result += self.level.print("def Import(dao, encoding=" + encoding +
", text_file='" + text_file +
"', hasHeader=True, sep='" +
self.order.sep[2] + "'):")
self.level.inc()
if self.order.sep[1] == 'CSV':
result += self._inject_import_csv()
else:
result += self._inject_import()
result += self.level.print("")
self.level.dec()
self.level.pop()
result += self.level.print("")
return result
def sql_create_table(self):
''' Translate the order into a field-driven SQL Table creation statement. '''
result = "CREATE TABLE IF NOT EXISTS " + self.order.table_name
result = result + '(ID INTEGER PRIMARY KEY AUTOINCREMENT,'
for key in self.fields:
if key.lower() == "id":
continue
result += ' '
result += key + " "
result += self.fields[key] + ","
result = result[0:len(result) - 1]
result = result + ');'
return result
def sql_insert_row(self):
''' Translate the order into a field-driven SQL Row creation statement. '''
result = "INSERT INTO " + self.order.table_name + " ("
final_keys = list()
for key in self.fields:
if key.lower() == "id":
continue
result += ' '
result += key + ","
final_keys.append(key)
result = result[0:len(result) - 1] # remove closing comma
result = result + ') VALUES ('
for val in final_keys:
result += '?,'
result = result[0:len(result) - 1] # remove closing comma
result = result + ');'
return result
def sql_update_row(self, key_name, fields_name):
''' Translate the order into a field-driven SQL Row Update statement. '''
result = "UPDATE " + self.order.table_name + " SET"
for key in self.fields:
if key.lower() == "id":
continue
result += ' '
result += key + "=?,"
result = result[0:len(result) - 1] # remove closing comma
result = result + ' WHERE ID = {};".format(' + key_name + "), " + fields_name + ")"
return result
def _inject_import_csv(self):
result = ''
result += self.level.print('import csv')
result += self.level.print(
"with open(text_file, 'r', encoding=encoding) as fh:")
self.level.inc()
result += self.level.print('lines = csv.reader(fh)')
result += self.level.print('for line in lines:')
self.level.inc()
result += self.level.print('dao.insert(line)')
self.level.dec()
self.level.dec()
result += self.level.print('return True')
return result
def _inject_import(self):
result = ''
result += self.level.print("try:")
self.level.inc()
result += self.level.print('# dao.open()')
result += self.level.print(
"with open(text_file, encoding=encoding) as fh:")
self.level.inc()
result += self.level.print("line = fh.readline().strip()")
result += self.level.print("if hasHeader is True:")
self.level.inc()
result += self.level.print("line = fh.readline().strip()")
self.level.dec()
result += self.level.print("while len(line) != 0:")
self.level.inc()
result += self.level.print("if dao.insert(line.split(sep)) is False:")
self.level.inc()
result += self.level.print("return False")
self.level.dec()
result += self.level.print("line = fh.readline().strip()")
self.level.dec()
self.level.dec()
result += self.level.print("# dao.close()")
result += self.level.print("return True")
self.level.dec()
result += self.level.print("except:")
self.level.inc()
result += self.level.print("pass")
self.level.dec()
result += self.level.print("return False")
return result
def __init__(self, order, fields):
if isinstance(order, OrderClass) is False:
raise GenOrder("Error: ClassOrder is missing")
self.order = order
self.fields = fields
self.level = CodeLevel()
class SqliteCrud:
def __init__(self, order, fields):
if isinstance(order, OrderClass) is False:
raise GenOrder("Error: ClassOrder is missing")
self.order = order
self.fields = fields
self.level = CodeLevel()
def code_class_template(self, data_file, sep=','):
self.level.set(0)
result = self.level.print("import sqlite3\n\n\nclass " + self.order.class_name + ":")
result += self.level.print("")
self.level.inc()
result += self.level.print("def __init__(self):")
self.level.inc()
result += self.level.print("self.db = '" + self.order.db_name +"'")
result += self.level.print("self.conn = None")
result += self.level.print("self.curs = None")
result += self.level.print("self.bOpen = False")
result += self.level.print("self.fields = " + str(self.fields))
result += self.level.print("self.table_name = '" + self.order.table_name + "'")
result += self.level.print("")
self.level.dec()
result += self.level.print("def open(self):")
self.level.inc()
result += self.level.print("if self.bOpen is False:")
self.level.inc();
result += self.level.print("self.conn = sqlite3.connect(self.db)")
result += self.level.print("self.curs = self.conn.cursor()")
result += self.level.print("self.bOpen = True")
self.level.dec()
result += self.level.print("return True")
result += self.level.print("")
self.level.dec()
result += self.level.print("def close(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print("self.conn.commit()")
result += self.level.print("self.bOpen = False")
self.level.dec()
result += self.level.print("return True")
result += self.level.print("")
self.level.dec()
result += self.level.print("def count(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('res = self.curs.execute("SELECT count(*) FROM ' + self.order.table_name + ';")')
result += self.level.print("return res.fetchone()[0]")
self.level.dec()
result += self.level.print("return -1")
result += self.level.print("")
self.level.dec()
result += self.level.print("def drop_table(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('self.curs.execute("DrOp TaBLe IF EXISTS ' + self.order.table_name + ';")')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def create_table(self):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('self.curs.execute("' + self.sql_create_table() + '")')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def insert(self, fields):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('self.curs.execute("' + self.sql_insert_row() + '", fields)')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def delete(self, primary_key):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('self.curs.execute("DELETE from ' + self.order.table_name + ' WHERE ID = ?;", [primary_key])')
result += self.level.print("return True")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
result += self.level.print("def select(self, sql_select):")
self.level.inc()
result += self.level.print("if self.bOpen:")
self.level.inc();
result += self.level.print('self.curs.execute(sql_select)')
result += self.level.print("zlist = self.curs.fetchall()")
result += self.level.print("for ref in zlist:")
self.level.inc();
result += self.level.print("yield ref")
self.level.dec()
self.level.dec()
result += self.level.print("return None")
result += self.level.print("")
self.level.dec()
self.level.push()
result += self.level.print("@staticmethod")
result += self.level.print("def Import(dao, data_file='" + data_file + "', hasHeader=True, sep='" + sep + "'):")
self.level.inc()
result += self.level.print("try:")
self.level.inc()
result += self.level.print('# dao.open()')
result += self.level.print("with open(data_file) as fh:")
self.level.inc()
result += self.level.print("line = fh.readline().strip()")
result += self.level.print("if hasHeader is True:")
self.level.inc()
result += self.level.print("line = fh.readline().strip()")
self.level.dec()
result += self.level.print("while len(line) is not 0:")
self.level.inc()
result += self.level.print("if dao.insert(line.split(sep)) is False:")
self.level.inc()
result += self.level.print("return False")
self.level.dec()
result += self.level.print("line = fh.readline().strip()")
self.level.dec()
self.level.dec()
result += self.level.print("# dao.close()")
result += self.level.print("return True")
self.level.dec()
result += self.level.print("except:")
self.level.inc()
result += self.level.print("pass")
self.level.dec()
result += self.level.print("return False")
result += self.level.print("")
self.level.dec()
self.level.pop()
result += self.level.print("")
return result
def sql_create_table(self):
result = "CREATE TABLE IF NOT EXISTS " + self.order.table_name
result = result + '(ID INTEGER PRIMARY KEY AUTOINCREMENT,'
for ss, val in enumerate(self.fields):
result += ' '
result += val[0] + " "
result += val[1] + ","
result = result[0:len(result) - 1]
result = result + ');'
return result
def sql_insert_row(self):
result = "INSERT INTO " + self.order.table_name + " ("
for val in self.fields:
result += ' '
result += val[0] + ","
result = result[0:len(result) - 1]
result = result + ') VALUES ('
for val in self.fields:
result += '?,'
result = result[0:len(result) - 1]
result = result + ');'
return result
def __init__(self, order, fields):
self.order = order
self.fields = fields
self.level = CodeLevel()