def tablegenrator(tablename = None, fields = None, \
fieldstypes = None, options = None):
""" creates sql to create table, or if table exists, read its variables """
if tablename:
tab['tbname'] = tablename
else:
tab['tbname'] = cli.show(sep(t['provide'], t["tbname"]))
do_create = True
#if table exists just read its data, inform user and return
try:
x = tab["cur"].execute("SELECT * FROM sqlite_master \
WHERE name='%s'" %tab['tbname'])
data = [r for r in x]
if data:
do_create = False
#for r in data: print "tb items", r
# since string is "create table = 14 + tablename + id autoinc = 47"
scheme = data[0][4][14 + len(tab['tbname']) + 47:-1]
tab['tbheaders'] = ",".join( [ x[0:x.index(" ")] \
for x in scheme.split(",") ] )
tab['tbheaders'] = "(" + tab['tbheaders'] + ")"
except Exception as e: # should raise?
print e # Exception might mean that incorrectly parse table
if do_create:
# cant actually assume that if tbheaders are set, we wont
# want to reset them...unless they'd be passed as fields, hmm
if fields:
tab['tbheaders'] = fields
fields = tab['tbheaders'].split(";")
else:
tab['tbheaders'] = cli.show(sep(t['provide'],
t["fields"],
t['separated_by']))
fields = tab['tbheaders'].split(";")
try:
tab['tbheaders'] = ",".join(tab['tbheaders'].split(";"))
tab['tbheaders'] = "(" + tab['tbheaders'] + ")"
except Exception as e:
print t['notallowed']
return
variable = []
for f in fields:
variable.append((f, cli.show(sep(t['provide'], "sql typ dla", f, \
"\nDATATIME, TEXT, INT, INTEGER, CHAR(39), VAR(3), REAL etc"))))
# TODO : +options if any
sql = "CREATE TABLE" + " " + tab['tbname'] + " "
sql += "(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
for v in variable:
sql += sep(v[0], v[1]) + ","
sql = sql[:-1] #strip last ","
sql += ");"
return sql
def delete():
""" deletes rows in a loop """
while 1:
ret = cli.show(t['del_extended'])
if ret == "0":
pokaz()
continue
elif ret == "*":
tab["cur"].execute("DELETE FROM %s" % (tab["tbname"]) )
elif ret == "-" or ret[0] == "-":
return
elif len(ret) > 2:
# hah, this should totally fail
try:
r1 = ",".join([ x for x in ret.split(",") if not "-" in x ])
# this particulary should explode...but hey it was fun
# this - was even funnier,but would not handle multi digits like 34-55.
# r1 += ",".join( ("".join(str(range(int(x[0]),int(x[2:])+1)))) for x in ret.split(",") if "-" in x )
# But hey, this beauty below? Can do multidigits. Although range "eats", last one like 1-3 = 1,2...sadface.
r1 += ",".join( (",".join(map(str, range(*map(int, x.split("-"))))) for x in ret.split(",") if "-" in x ))
ret = r1
print ret, " ok ? "
except Exception:
print t['failed']
else:
tab["cur"].execute("DELETE FROM %s WHERE ID = %s"
% (tab["tbname"], ret) )
break
tab["conn"].commit()
def insert():
"""handles inserts in funny way, generating placeholder from tableheader"""
try:
if tab["tbheaders"] == None:
raise Exception("tbheaders not set")
except Exception:
print t['sel_st']
return
# TODO, optimize for many inserts
# rows = [ ('itm', itm, 'itm'),
# ('itm', itm, 'itm'),
# ('itm', itm, 'itm') ]
# c.executemany('insert into table values (?,?,?,?,?)', rows )
# connection.commit()
# ouch the pain. Why did i do that like that.
ret = [ tuple( cli.show(sep( t['provide'],
"format: (X; Y; 3; Z),",
tab["tbheaders"])).split(";")
)
]
prep = lambda: "".join( [ "?," for x in tab["tbheaders"].split(",") ] )[:-1]
for item in ret:
sql = "INSERT INTO %s %s VALUES(%s)" \
% ( tab["tbname"], tab['tbheaders'], prep() )
try:
tab["cur"].execute(sql, item)
tab["conn"].commit()
except sqlite3.ProgrammingError:
print t['notallowed']
def select():
""" user selects db and table. Perhaps should split those two """
print t['sel_db']
_dbs = [ x for x in os.listdir(".") if x.endswith(".db") ]
if _dbs:
print t["select_file_or_input"]
ret = cli.show(_dbs)
# :/ not proud. Since using cli with digits to control flow,"\0" is needed
skipthis = False
if ret[0] == "/":
skipthis = True
ret = ret[1:]
if ret.isdigit() and not skipthis:
try:
ret = _dbs[int(ret)]
except IndexError:
print sep(t['failed'], t['will_try_fix'])
ret = "tmp" + ".db"
elif len(ret) < 3:
ret += ".db"
elif len(ret) >= 3 and ret[-3:0] != ".db":
ret += ".db"
else:
print t['sel_no'] % ret
ret = "_" + ret + ".db"
tab["dbname"] = ret
createdb()
tabs = tab["cur"].execute("SELECT * FROM sqlite_master WHERE type='table'")
_tbs = [ x[2] for x in tabs ]
print t["sel_tb"]
if _tbs:
print t["select_file_or_input"]
ret = cli.show(_tbs)
skipthis = False
if ret[0] == "/":
skipthis = True
ret = ret[1:]
if ret.isdigit() and not skipthis:
try:
ret = _tbs[int(ret)]
except IndexError:
print sep(t['failed'], t['will_try_fix'])
ret = "tmp"
tab["tbname"] = ret
createtab()
return 0
def rawsql(sql = None):
""" just dont play around and execute raw sql on this db """
if not sql:
sql = cli.show(t['provide'])
try:
tab["cur"].execute(sql)
except:
try:
tab["cur"].execute(sql)
except sqlite3.ProgrammingError as _e:
print "prawdopodobnie niepoprawny sql / probably malformed sql"
raise _e
