def insert(self, table, sels):
try:
self.connect()
self.session.add(self.base.classes[table](**dict(sels)))
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def connect(self):
try:
self.cn = ps2.connect(host=self.host,
port=self.port,
database=self.database,
user=self.user,
password=self.password)
self.cr = self.cn.cursor(cursor_factory=psycopg2.extras.DictCursor)
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
def select_all(self, table):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
try:
self.connect()
qr = self.session.query(self.base.classes[table]).limit(1000)
data = [[getattr(q, c) for c in col_names] for q in qr]
view.print_table(col_names, col_types, pkeys, fkeys, data)
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def connect(self):
try:
self.engine = sqlal.create_engine('postgresql://'+self.user+':' +
self.password+'@'+self.host+':'+self.port+'/'+self.database)
self.meta = sqlal.MetaData()
self.meta.reflect(self.engine)
self.base = automap_base(metadata=self.meta)
self.base.prepare()
self.session = Session(self.engine)
except Exception as e:
view.error(e)
controller.selection()
def delete(self, table, where):
try:
self.connect()
qr = self.session.query(
self.base.classes[table]).filter(text(where)).all()
for tq in qr:
self.session.delete(tq)
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def update(self, table, sels, where):
try:
self.connect()
qr = self.session.query(self.base.classes[table]).filter(text(where)).all()
for tq in qr:
for cs,cv in sels:
setattr(tq, cs, cv)
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def delete(self, table, where):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("DELETE FROM {} {};").format(
psycopg2.sql.Identifier(table),
(psycopg2.sql.SQL("WHERE " + where)
if where else psycopg2.sql.SQL(""))))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def select_all(self, table):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("SELECT * FROM public.{};").format(
psycopg2.sql.Identifier(table)))
data = self.cr.fetchall()
view.print_table(col_names, col_types, pkeys, fkeys, data)
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def update(self, table, sels, where):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL("UPDATE public.{} SET {} {};").format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([s[0] + "=%s" for s in sels])),
(psycopg2.sql.SQL("WHERE " + where)
if where else psycopg2.sql.SQL(""))),
tuple([s[1] for s in sels]))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def insert(self, table, sels):
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL(
"INSERT INTO public.{} ({}) VALUES ({});").format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([s[0] for s in sels])),
psycopg2.sql.SQL(", ".join(
["%s" for i in range(len(sels))]))),
tuple([s[1] for s in sels]))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()
def gen_random(self, table, gen_len):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
self.delete(table, '')
try:
self.connect()
self.session.execute(text("INSERT INTO public.\"{}\" (SELECT {} FROM generate_series(1, {}));"
.format(table, ", ".join(["(SELECT {} FROM public.\"{}\" ORDER BY RANDOM()+generate_series LIMIT 1)"
.format([f[2] for f in fkeys if f[0] == c][0],
[f[1] for f in fkeys if f[0] == c][0])
if c in [f[0] for f in fkeys] else (("generate_series" if 'INTEGER' in t or 'NUMERIC' in t else "generate_series::text")
if c in pkeys else (("NOW()+(random()*(interval '90 days'))+'30 days'" if 'TIMESTAMP' in t
else ("cast(random()::int as boolean)" if t == 'BOOLEAN'
else ("SUBSTRING(md5(random()::text),1,5)" if 'TEXT' in t or 'VARCHAR' in t
else "(random()*1000000)::int")))))
for c, t in zip(col_names, col_types)]), gen_len)))
self.session.commit()
except Exception as e:
view.error(e)
controller.selection()
finally:
self.close()
def gen_random(self, table, gen_len):
col_names, col_types, pkeys, fkeys = self.get_col_info(table)
self.delete(table, '')
try:
self.connect()
self.cr.execute(
psycopg2.sql.SQL(
"INSERT INTO public.{} (SELECT {} FROM generate_series(1, {}));"
).format(
psycopg2.sql.Identifier(table),
psycopg2.sql.SQL(", ".join([(psycopg2.sql.SQL(
"(SELECT {} FROM public.{} ORDER BY RANDOM()+generate_series LIMIT 1)"
).format(
psycopg2.sql.SQL([f[2] for f in fkeys
if f[0] == c][0]),
psycopg2.sql.Identifier([
f[1] for f in fkeys if f[0] == c
][0])).as_string(self.cn) if c in [
f[0]
for f in fkeys
] else (
("generate_series" if t in ['integer', 'numeric']
else "generate_series::text") if c in pkeys else
(("NOW()+(random()*(interval '90 days'))+'30 days'"
if 'timestamp' in t else
("cast(random()::int as boolean)" if t ==
'boolean' else
("SUBSTRING(md5(random()::text),1,5)" if t in
['text', 'character varying'
] else "(random()*1000)::int")))
))) for c, t in zip(col_names, col_types)])),
psycopg2.sql.SQL(gen_len)))
self.cn.commit()
except (Exception, ps2.Error) as e:
view.error(e)
controller.selection()
finally:
self.close()