----- Original Message -----
From: "Chris Angelico" <ros...@gmail.com>
Cc: <python-list@python.org>
Sent: Thursday, January 15, 2015 2:33 PM
Subject: Re: MySQL connections
On Thu, Jan 15, 2015 at 10:59 PM, Jacob Kruger <ja...@blindza.co.za>
wrote:
Tried generating .exe with both cx_freeze, and pyInstaller, and the code
itself, and both versions of executable generate errors, same as running
it
from command line - only difference is the source of the error mentioned
in
error message then varies from a2m.exe and python.exe , and even if
compile
it on machine where all works, and then copy .exe back to this primary
machine, then get same error - think it must relate to something else on
this machine, but can't track it down.
Okay. Ignore the .exe versions, and just work with what happens when
you run the .py files. If it fails as part of a .py file, post the
failing file.
If you want to check it out, have attached the full code file - might be a
bit messy/large - but, effectively, right at bottom, launch an instance of
the class a2m, passing through arguments, and then from within __init__ call
convertProcess function, which then initiates process, harvesting sort of
rendition/version of structure out of MS access database file, makes call to
convertSQL to generate structural SQL script, and save it to a file, which
then calls convertData function to generate insert statements to populate
database, and then that makes a call to convertExport, if you passed a
command line argument in requesting mysql, and that's the current issue
function - have stripped most of actual functionality out of it, since am
just testing, so the first 16 lines of that function are what's relevant at
moment - think shouldn't rely on any other self. objects/attributes as such
either, unless step-through process is an issue.
And, tried cleaning it up a bit - replaced tab indentation character with
double spaces, but, code might not look perfect/be perfectly clean as of
yet.
Jacob Kruger
Blind Biker
Skype: BlindZA
"Roger Wilco wants to welcome you...to the space janitor's closet..."
import pypyodbc, os, sys
import copy, warnings
import pymysql, time, pickle
from unidecode import unidecode
def sTimeDiffFormat(i_time1, i_time2):
i_total_time = i_time2 - i_time1 if i_time2 > i_time1 else i_time1 - i_time2
i_total_time = int(i_total_time)
#time.strftime("%H:%M:%S", time.gmtime(i_total_time))
i_hours = 0
i_minutes = 0
if i_total_time >= 3600:
i_hours = int(i_total_time / 3600)
i_total_time = i_total_time - (i_hours * 3600)
if i_total_time > 60:
i_minutes = int(i_total_time/60)
i_total_time = i_total_time - (i_minutes * 60)
s_out = ""
if i_hours > 0: s_out = "{0} hours ".format(i_hours)
if i_minutes > 0: s_out = s_out + "{0} minutes ".format(i_minutes)
s_out = s_out + "{0} seconds".format(i_total_time)
return s_out
#end of sTimeDiffFormat
class a2m():
s_mdb = ""
s_mdb_pass = ""
s_mdb_conn = ""
bl_mdb_conn = False
d_db_tables = {}
cn_mdb = None
s_structure_sql = ""
d_db_data = {}
l_field_types = []
s_target = ""
s_target_value = ""
s_sql_struct_file = ""
s_sql_data_file = ""
def __init__(self, s_mdb, s_mdb_pass, s_target, s_target_value):
self.s_mdb = str(s_mdb) if len(str(s_mdb)) > 0 else ""
self.s_mdb_pass = str(s_mdb_pass) if len(str(s_mdb_pass)) > 0 else ""
if self.s_mdb_pass == "#": self.s_mdb_pass = ""
self.s_target = str(s_target) if len(str(s_target))>0 else ""
self.s_target_value = str(s_target_value) if len(str(s_target_value))>0 else
""
self.s_mdb_conn = ""
self.bl_mdb_conn = False
if self.s_mdb != "" and self.s_target != "" and self.s_target_value != "":
self.convertProcess()
#end of __init__
def shiftID(self, l_columns):
if l_columns.count("ID")>0:
l_columns.remove("ID")
l_columns.insert(0, "ID")
return l_columns
#end of shiftID
def dictDBStructure(self, cur):
d_out = {}
cur_tables = cur.tables(tableType="table").fetchall()
s_tables = ""
for l_table in cur_tables:
s_tables += str(l_table[2]) + "|"
l_tables = s_tables.split("|")
if len(l_tables) > 1: l_tables.pop(len(l_tables)-1)
for s_table in l_tables:
d_out[s_table] = {}
#s_columns = ""
#INTEGER, SMALLINT, VARCHAR, LONGCHAR, CURRENCY, BIT, DATETIME, COUNTER?
for col in cur.columns(table=s_table):
s_col_name = str(col.get("column_name"))
s_col_type = str(col.get("type_name"))
i_col_size = int(col.get("column_size"))
if list(d_out[s_table].keys()).count(s_col_name) < 1:
d_out[s_table][s_col_name] = {"column_name": s_col_name, "column_type":
s_col_type, "column_size": i_col_size}
return d_out
#end of dictDBStructure
def pullDataTypes(self, evt):
if len(self.s_mdb) > 0:
s_path = self.s_mdb
s_pwd = self.DialogInput(title="Password", label="Password (if required):",
value="")
if len(s_pwd) > 0: s_pwd = " pwd={0};".format(s_pwd)
self.s_mdb_conn = "Driver=Microsoft Access Driver (*.mdb, *.accdb);" + s_pwd +
" DBQ=" + s_path
try:
self.cn_mdb = pypyodbc.connect(self.s_mdb_conn)
self.bl_mdb_conn = True
cur = self.cn_mdb.cursor()
self.d_db_tables = self.dictDBStructure(cur)
#field_name-pypyodbcType-python valueType
#---
#ID - COUNTER - int
#text_one - VARCHAR - unicode
#double_one - DOUBLE - float
#byte_one - BYTE - int
#real_one - DECIMAL - <class decimal.Decimal>
#longbinary_one - LONGBINARY - bytearray
#int_one - INTEGER - int
#smallint_one - SMALLINT - int
#decimal_one - DECIMAL - <class decimal.Decimal>
#currency_one - CURRENCY - <class decimal.Decimal>
#datetime_one - DATETIME - datetime.datetime
#memo_one - LONGCHAR - unicode
#yes_no_one - BIT - bool
#hyperlink_one - LONGCHAR - unicode
#lookup_one - VARCHAR - unicode
#for insertion/update via pypyodbc.cursor.execute - pypyodbc.binary(data)
l_types = []
for s_table in self.d_db_tables.keys():
for s_column in self.d_db_tables[s_table].keys():
if l_types.count(self.d_db_tables[s_table][s_column]["column_type"])
< 1:
l_types.append(self.d_db_tables[s_table][s_column]["column_type"])
s_types = str(l_types).replace("[", "").replace("]", "").replace(", ",
"\n").replace("'", "")
self.DialogShow(title="column types", message=s_types)
self.cn_mdb.close()
self.bl_mdb_conn = False
except Exception as exc:
s_exc = str(exc.args)
exc_type, exc_obj, tb = sys.exc_info()
s_exc += "lineNo: " + str(tb.tb_lineno)
s_exc += str(exc_obj)
self.DialogShow("exception", message=s_exc)
#end of pullDataTypes
def convertProcess(self):
self.s_mdb = str(os.path.realpath(self.s_mdb))
self.d_db_tables = {}
if len(self.s_mdb) > 0:
s_path = self.s_mdb
s_pwd = self.s_mdb_pass
if len(s_pwd) > 0: s_pwd = " pwd={0};".format(s_pwd)
self.s_mdb_conn = "Driver=Microsoft Access Driver (*.mdb, *.accdb);" + str(s_pwd) +
" DBQ=" + str(s_path)
try:
self.cn_mdb = pypyodbc.connect(self.s_mdb_conn)
self.bl_mdb_conn = True
cur = self.cn_mdb.cursor()
#self.d_db_tables = self.addColumnDBStructure(cur)
#self.d_db_tables = self.classedDBStructure(cur)
self.d_db_tables = self.dictDBStructure(cur)
if len(self.d_db_tables) > 0:
for s in self.d_db_tables.keys():
pass #print(s + ": " + str(self.d_db_tables[s].keys()))
print("table count: " + str(len(self.d_db_tables)))
s_out = ""
for s_table in list(self.d_db_tables.keys()):
s_out += "[" + s_table + "]\r\n---\r\n"
s_out += str(list(self.d_db_tables[s_table].keys())) + "\r\n---\r\n"
for s_col in list(self.d_db_tables[s_table].keys()):
d_col = self.d_db_tables[s_table][s_col]
s_out += s_col + "\t" + d_col["column_type"]
if d_col["column_size"] != 0: s_out += " (" + str(d_col["column_size"]) +
")"
s_out += "\r\n"
s_out += "---\r\n"
f = open(self.s_target_value + ".txt", "w")
f.write(s_out)
f.close()
print(self.s_target_value + ".txt info file written")
cur.close()
self.cn_mdb.close()
self.bl_mdb_conn = False
self.convertSQL()
except Exception as exc:
s_exc = str(exc.args)
exc_type, exc_obj, tb = sys.exc_info()
s_exc += "\nline no: " + str(tb.tb_lineno)
#lbc.DialogShow(title="errorMessage", message=s_exc)
print(s_exc)
else:
print("missing .mdb/.sdb file")
return None
#sys.exit()
#end of convertProcess function
def convertSQL(self):
try:
if len(self.d_db_tables) > 0:
s_table_sql = "DROP TABLE IF EXISTS `#table#`;\n"
s_table_sql += "CREATE TABLE `#table#` (\r\n#fields##index#"
#s_table_index_sql = "INDEX (`#id#`),\r\n PRIMARY KEY (`#id#`)"
s_table_index_sql = "INDEX (`#id#`)"
s_table_sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8;\r\n"
s_tables_sql = ""
for s_table in self.d_db_tables.keys():
s_id = ""
d_each_table = self.d_db_tables[s_table]
#re-order columns to put id/counter first
l_table_columns = list(d_each_table.keys())
#l_columns = []
l_table_columns = self.shiftID(l_table_columns)
s_fields_sql = ""
for s_col in l_table_columns:
d_col = self.d_db_tables[s_table][s_col]
#INTEGER, SMALLINT, VARCHAR, LONGCHAR, CURRENCY, BIT, DATETIME,
COUNTER?
if d_col["column_type"] == "COUNTER":
s_fields_sql = s_fields_sql + "`{0}` INTEGER NOT NULL AUTO_INCREMENT,
\r\n".format(d_col["column_name"])
if s_id == "": s_id = d_col["column_name"]
elif d_col["column_type"]=="INTEGER": #int
s_fields_sql = s_fields_sql + "`{0}` INTEGER,
\r\n".format(d_col["column_name"])
elif d_col["column_type"]=="SMALLINT": #int16
s_fields_sql = s_fields_sql + "`{0}` SMALLINT,
\r\n".format(d_col["column_name"])
elif d_col["column_type"]=="BYTE": #bigint
s_fields_sql = s_fields_sql + "`{0}` BIGINT,
\r\n".format(d_col["column_name"])
elif d_col["column_type"] == "VARCHAR":
s_fields_sql = s_fields_sql + "`{0}` VARCHAR ({1}),
\r\n".format(d_col["column_name"], str(d_col["column_size"]))
elif d_col["column_type"] == "LONGCHAR": #longtext
s_fields_sql = s_fields_sql + "`{0}` LONGTEXT,
\r\n".format(d_col["column_name"])
elif d_col["column_type"] == "DATETIME": #datetime
s_fields_sql = s_fields_sql + "`{0}` DATETIME,
\r\n".format(d_col["column_name"])
elif d_col["column_type"] == "CURRENCY" or d_col["column_type"] ==
"REAL": #float
s_fields_sql = s_fields_sql + "`{0}` FLOAT,
\r\n".format(d_col["column_name"])
elif d_col["column_type"] == "BIT": #bit
s_fields_sql = s_fields_sql + "`{0}` BIT,
\r\n".format(d_col["column_name"])
elif d_col["column_type"] == "LONGBINARY": #longblob
s_fields_sql = s_fields_sql + "`{0}` LONGBLOB,
\r\n".format(d_col["column_name"])
else: #others?
s_fields_sql = s_fields_sql + "`{0}` {1}, \r\n".format(d_col["column_name"],
d_col["column_type"])
#s_fields_sql = s_fields_sql[:len(s_fields_sql)-4]
if s_id != "":
s_tables_sql = s_tables_sql + s_table_sql.replace("#table#", s_table).replace("#fields#",
s_fields_sql).replace("#index#", s_table_index_sql.replace("#id#", s_id)) + "\r\n"
else:
s_tables_sql = s_tables_sql + s_table_sql.replace("#table#", s_table).replace("#fields#",
s_fields_sql[:-4]).replace("#index#", "") + "\r\n"
self.s_sql_struct_file = self.s_target_value + "SQL.txt"
f = open(self.s_sql_struct_file, "w")
f.write(s_tables_sql)
f.close()
self.s_structure_sql = s_tables_sql
print("Structure SQL written to " + self.s_sql_struct_file)
self.convertData()
else:
print("Nothing to work with - No table structures to process")
except Exception as exc:
s_exc = str(exc.args)
exc_type, exc_obj, tb = sys.exc_info()
s_exc += "\nline no: " + str(tb.tb_lineno)
#lbc.DialogShow(title="errorMessage", message=s_exc)
print(s_exc)
#end of convertSQL function
def sRowInsertSQL(self, s_table, s_columns, l_data):
s_busy = ""
s_col_name = ""
s_val_type = ""
try:
s_out = ""
l_columns = s_columns.replace("[", "").replace("]", "").split(", ")
#l_data is a list of tuples that contain actual data
for J in range(len(l_data)):
s_fields = ""
s_values = ""
for I in range(len(l_columns)):
d_col = self.d_db_tables[s_table][l_columns[I]]
s_busy = "about to retrieve column name"
s_fields += "`" + d_col["column_name"] + "`, "
s_col_name = d_col["column_name"]
#field_name-pypyodbcType-python valueType
#---
#ID - COUNTER - int
#text_one - VARCHAR - unicode
#double_one - DOUBLE - float
#byte_one - BYTE - int
#real_one - DECIMAL - <class decimal.Decimal>
#longbinary_one - LONGBBLOB - bytearray
#int_one - INTEGER - int
#smallint_one - SMALLINT - int
#decimal_one - DECIMAL - <class decimal.Decimal>
#currency_one - CURRENCY - <class decimal.Decimal>
#datetime_one - DATETIME - datetime.datetime
#memo_one - LONGCHAR - unicode
#yes_no_one - BIT - bool
#hyperlink_one - LONGCHAR - unicode
#lookup_one - VARCHAR - unicode
#for insertion/update via pypyodbc.cursor.execute -
pypyodbc.binary(data)
if d_col["column_type"] in ("VARCHAR", "LONGCHAR", "CHAR"):
s_busy = "about to retrieve text type data value"
s_val = unidecode(l_data[J][I]) if l_data[J][I] != None else ""
if s_val == None: s_val = ""
s_val_type = str(type(s_val))
if self.l_field_types.count(s_val_type) < 1:
self.l_field_types.append(s_val_type)
s_busy = "about to unicode.decode it if unicode, or else just str
datetime"
#'abc'.decode('utf-8') # str to unicode
#u'abc'.encode('utf-8') # unicode to str
s_busy = "about to add it to s_values"
s_values += "'" + s_val.replace("\\", "\\\\").replace("'", chr(92) + "'").replace("\"", chr(92) +
"\"").replace(";", "") + "', "
elif d_col["column_type"] == "DATETIME":
s_busy = "about to retrieve datetime type data value"
s_val = str(l_data[J][I]) if l_data[J][I] != None else ""
s_val = unidecode(s_val) if l_data[J][I] != None else ""
if s_val == None: s_val = ""
s_val_type = str(type(s_val))
if self.l_field_types.count(s_val_type) < 1:
self.l_field_types.append(s_val_type)
s_busy = "about to unicode.decode it if unicode, or else just str
datetime"
#'abc'.decode('utf-8') # str to unicode
#u'abc'.encode('utf-8') # unicode to str
s_busy = "about to add it to s_values"
if s_val == "None" or s_val == "":
s_values += "NULL, "
else:
s_values += "'" + s_val.replace("\\", "\\\\").replace("'", chr(92) + "'").replace("\"", chr(92) +
"\"").replace(";", "") + "', "
elif d_col["column_type"] in ("INTEGER", "SMALLINT", "COUNTER",
"BYTE"):
s_busy = "about to add int value"
s_val = unidecode(u"" + str(l_data[J][I])) if l_data[J][I] != None else
"0"
s_values += s_val + ", "
elif d_col["column_type"] in ("CURRENCY", "DECIMAL", "DOUBLE",
"REAL"):
s_busy = "about to add float value"
s_val = l_data[J][I]
s_val = unidecode(s_val)
f_val = 0.0 if s_val in ("", "None") else float(s_val)
s_values += unidecode(f_val) + ", "
elif d_col["column_type"] == "BIT":
s_busy = "about to add bit value " + str(l_data[J][I]).lower()
s_val = u"1" if l_data[J][I] else u"0"
s_val = unidecode(s_val).lower()
s_val = "1" if s_val == "true" else "0"
s_values += s_val + ", "
elif d_col["column_type"] == "LONGBINARY":
s_busy = "about to add longbinary value..."
s_values += "0x" + str(l_data[J][I]).encode("hex").replace("\\", "\\\\") +
", "
else:
print("big shiznit: " + str(d_col["column_name"]) + "-" +
str(d_col["column_type"]))
#remove ending ', ' at end of fields and values
s_fields = s_fields[:-2] if len(s_fields) > 2 else ""
s_values = s_values[:-2] if len(s_values) > 2 else ""
s_out += "INSERT INTO `{0}` ({1}) values ({2});\n".format(s_table,
s_fields, s_values)
return s_out
except Exception as exc:
print(str(exc.args))
exc_type, exc_obj, tb = sys.exc_info()
print("lineNo: " + str(tb.tb_lineno))
print(str(exc_obj))
print("busy with: " + s_busy + "\ntable: " + s_table + "\ncolumn name: " +
s_col_name + "\nvalue type: " + s_val_type)
#sys.exit()
return ""
#end of sRowInsertSQL
def convertData(self):
try:
if len(self.d_db_tables) > 0:
if not self.bl_mdb_conn:
self.cn_mdb = pypyodbc.connect(self.s_mdb_conn)
self.bl_mdb_conn = True
s_data_sql = ""
cur = self.cn_mdb.cursor()
s_out = ""
for s_table in self.d_db_tables.keys():
d_columns = self.d_db_tables[s_table]
#read data into recursive lists
l_table_columns = list(d_columns.keys())
#INTEGER, SMALLINT, VARCHAR, LONGCHAR, CURRENCY, BIT, DATETIME,
COUNTER?
l_table_columns = self.shiftID(l_table_columns)
s_columns = "[" + str(l_table_columns).replace("[", "").replace("]", "").replace("'",
"").replace(", ", "], [") + "]"
#print ("SQL select statement: " + "select " + s_columns + " from " + s_table +
";")
cur_rows = cur.execute("select " + s_columns + " from " + s_table +
";")
cur_data = cur_rows.fetchall()
#self.d_db_data[s_table] = copy.copy(cur_data)
self.d_db_data[s_table] = cur_data
if len(self.d_db_data[s_table])>0:
#print("about to call sRowInsertSQL func")
s_data_sql += self.sRowInsertSQL(s_table, s_columns,
self.d_db_data[s_table])
#cur_rows.close()
if len(s_data_sql)>0:
self.s_sql_data_file = self.s_target_value + "SQLData.txt"
f = open(self.s_sql_data_file, "w")
f.write(s_data_sql)
f.close()
f = open(self.s_target_value + "FieldDataTypes.txt", "w")
f.write(str(self.l_field_types))
f.close()
print("Data SQL statements for data insertion written to " + self.s_sql_data_file +
", and " + self.s_target_value + "FieldDataTypes.txt has all field types in it")
self.cn_mdb.close()
self.bl_mdb_conn = False
if self.s_target=="mysql":
warnings.simplefilter("ignore")
self.convertExport()
warnings.simplefilter("default")
else:
print("Done!")
#sys.exit()
return None
else:
print("Data issue - Nothing to work with")
except Exception as exc:
s_exc = str(exc.args)
exc_type, exc_obj, tb = sys.exc_info()
s_exc += "\nline no: " + str(tb.tb_lineno)
#lbc.DialogShow(title="errorMessage", message=s_exc)
print(s_exc)
exc_type, exc_obj, tb = sys.exc_info()
print("line number: " + str(tb.tb_lineno))
#end of convertData function
def convertExport(self):
try:
f_mysql = open("mysql.set", "rb")
d_mysql = pickle.load(f_mysql)
f_mysql.close()
cn = None
s_host = str(d_mysql["host"])
s_user = str(d_mysql["user"])
s_pass = str(d_mysql["password"])
s_db = self.s_target_value
cn = pymysql.connect(s_host, s_user, s_pass, s_db)
#cn.autocommit(True)
time.sleep(5)
print("about to close cn")
try:
cn.close()
except Exception as exc:
print("closing exc: " + str(exc))
finally:
sNada = input(".....")
#time.sleep(10)
print("closed")
sys.exit()
#if cn:
if True:
l_struct_errors = []
l_data_errors = []
i_struct = 0
i_data = 0
l_struct = []
l_data = []
s_time_taken = "aeons!"
#cn.close()
fPickle = open("testDataErrors.pickle", "wb")
pickle.dump(l_struct_errors, fPickle, 2)
pickle.dump(l_data_errors, fPickle, 2)
fPickle.close()
print("MySQL Results - {0} structure queries, and {1} data queries completed
- total of {2}".format(str(len(l_struct)), str(len(l_data)), s_time_taken))
print("MySQL Errors - {0} structure query errors, and {1} data query
errors".format(str(len(l_struct_errors)), str(len(l_data_errors))))
print("Done!")
#sys.exit()
return None
else:
print("Connection issue - There was a problem connecting to MySQL
server")
#sys.exit()
return None
except Exception as exc:
s_exc = str(exc.args)
exc_type, exc_obj, tb = sys.exc_info()
s_exc += "\nline no: " + str(tb.tb_lineno)
#lbc.DialogShow(title="errorMessage", message=s_exc)
print(s_exc)
exc_type, exc_obj, tb = sys.exc_info()
print(str(exc_obj))
print("line number: " + str(tb.tb_lineno))
#end of convertExport function
#end of a2m class
if __name__ == "__main__":
if len(sys.argv) >= 5:
s_mdb = str(sys.argv[1])
s_mdb_pass = str(sys.argv[2])
s_target = str(sys.argv[3]) if len(sys.argv)>=3 else ""
s_target_value = str(sys.argv[4]) if len(sys.argv)>=4 else ""
a2m(s_mdb, s_mdb_pass, s_target, s_target_value)
else:
print("you need to supply 4 arguments - MDB/SDB file name, mdb/sdb password - or
# for blank value, target type (sql or mysql), and target value (file_name.sql, or
database name)")
sys.exit()--
https://mail.python.org/mailman/listinfo/python-list
