def get_query_list_to_replicate(last_update):
dct_data_list = {}
lst_replications = []
time_stamp = ''
cursor = None
insert_sql = None
try:
select_sql = 'select `id`,`table`,`schema`,`query`,`type`,`time_stamp` from maticagent_replicate.replication_data where id > \''+str(last_update)+'\' order by `schema`, `table` '
print select_sql
cursor = cnx.cursor(dictionary=True)
cursor.execute(select_sql + "limit 10")
for row in cursor:
insert_sql = row['query']
schema = row['schema']
table = row['table']
dct_repliction = {}
dct_repliction['schema'] = schema
dct_repliction['table'] = table
dct_repliction['query'] = insert_sql
lst_replications.append(dct_repliction)
last_update = row['id']
time_stamp = row['time_stamp']
dct_data_list['queries'] =lst_replications
dct_data_list['last_update'] = last_update
dct_data_list['time_stamp'] = time_stamp
except mysql.connector.Error as err:
print(err)
cursor.close();
return dct_data_list
def count(self,table,params={},join='AND'):
# 根据条件统计行数
try :
sql = 'SELECT COUNT(*) FROM %s' % table
if params :
where ,whereValues = self.__contact_where(params)
sqlWhere= ' WHERE '+where if where else ''
sql+=sqlWhere
#sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
self.__display_Debug_IO(sql,tuple(whereValues)) #DEBUG
if self.DataName=='ORACLE':
cursor.execute(sql % tuple(whereValues))
else :
cursor.execute(sql,tuple(whereValues))
#cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
#except:
# raise BaseError(707)
except Exception as err:
try :
raise BaseError(707,err._full_msg)
except :
raise BaseError(707)
def update_replicated_database(dct_element_map):
dct_update_status = {}
row_count = 0
rows_processed = 0
cmd_id = dct_element_map['CID']
lst_replications = dct_element_map['replications']
for dct_replication in lst_replications:
table = dct_replication['table']
schema = dct_replication['schema']
cursor = None
lst_queries = dct_replication[schema+'.'+table]
try:
query = prepare_query(lst_queries,schema);
cursor = cnx.cursor()
cursor.execute(query)
# cnx.commit()
rows_processed+=1
except mysql.connector.Error as err:
print err
log_error_to_table(str(err)+'for query - '+query)
cursor.close()
row_count+=1
dct_update_status['row_count'] = row_count
dct_update_status['rows_processed'] = rows_processed
dct_update_status['cmd_id'] = cmd_id
return dct_update_status
def test_rawfetchall(self):
cursor = self.cnx.cursor(raw=True)
cursor.execute("SELECT 1")
try:
cursor.fetchall()
except errors.InterfaceError:
self.fail("fetchall() raises although result is available")
def update(self,table,data,params={},join='AND',commit=True,lock=True):
# 更新数据
try :
fields,values = self.__contact_fields(data)
if params :
where ,whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere= ' WHERE '+where if where else ''
cursor = self.__getCursor()
if commit : self.begin()
if lock :
sqlSelect="SELECT %s From `%s` %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere)
cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁
sqlUpdate = "UPDATE `%s` SET %s "% (table,fields) + sqlWhere
self.__display_Debug_IO(sqlUpdate,tuple(values)) #DEBUG
cursor.execute(sqlUpdate,tuple(values))
if commit : self.commit()
return cursor.rowcount
except Exception as err:
try :
raise BaseError(705,err._full_msg)
except :
raise BaseError(705,err.args)
def open():
querry = "select * from users where name = %s "
try:
cursor = cnx.cursor()
cursor.execute(querry, (request.form["username"],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
currentlyLocked[0] = False
logAction = "Opened the lock"
logDbAction(logUserId, logAction, logTime)
return "opend"
else:
logAction = "Tried to open already open lock"
logDbAction(logUserId, logAction, logTime)
return "Aleady Open"
else:
logAction = "tried to open the lock but denied due to invalid credentials"
logDbAction(logUserId, logAction, logTime)
return "denied"
except Exception, err:
print Exception, err
def test_columnnames_unicode(self):
"""Table names should unicode objects in cursor.description"""
exp = [("ham", 8, None, None, None, None, 0, 129)]
cursor = self.cnx.cursor()
cursor.execute("SELECT 1 as 'ham'")
cursor.fetchall()
self.assertEqual(exp, cursor.description)
def deleterequest(cnx, cursor, id, placeid, which, results):
if which == 0:
while 1:
featid = input("Please input the feature id from the above list you would like to update:\n")
isafeat = 0
for result in results:
if result[4] == featid:
isafeat = 1
if isafeat:
break
else:
print("Not a valid feature id from the results. Try again")
delete = ("INSERT INTO change_requests "
"(userid, featureid, changetype, streetid) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(delete, (id, featid, "delete", placeid))
cnx.commit()
print("Change request submitted")
elif which == 1:
while 1:
featid = "Please input the feature id from the above list you would like to update:"
isafeat = 0
for result in results:
if result[2] == featid:
isafeat = 1
if isafeat:
break
else:
print("Not a valid feature id from the results. Try again")
delete = ("INSERT INTO change_requests "
"(userid, featureid, changetype, intersectionid) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(delete, (id, featid, "delete", placeid))
cnx.commit()
print("Change request submitted")
def lock():
querry = ("select * from users where name = %s ")
try:
cursor=cnx.cursor()
cursor.execute(querry, (request.form['username'],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
logAction = "Attempted to lock already locked lock"
logDbAction(logUserId,logAction,logTime)
return "Already Locked"
else:
logAction = "Locked the lock"
logDbAction(logUserId,logAction,logTime)
currentlyLocked[0] = True
return "locked"
else:
logAction = "tried to lock the lock but denied due to invalid credentials"
logDbAction(logUserId,logAction,logTime)
return 'denied'
cursor.close
except Exception, err:
print Exception,err
def log_error_to_table(err):
sql = 'insert into '+mysql_server_controller_schema+'.client_errors (cmd,error,time_stamp) values (\''+element_map['CID']+'\',\''+err.replace('\'','|')+'\',now())'
try:
cursor = cnx.cursor()
cursor.execute(sql)
cnx.commit()
except mysql.connector.Error as err:
print err
return
def logDbAction(userid,logAction,logTime):
cursor = cnx.cursor()
insert = (userid,logAction,logTime)
querry = ("insert into logs (userid, action, time) VALUES (%s,%s, %s)")
cursor.execute(querry, insert)
result = cursor.fetchall
print(cursor.statement + " " + str(cursor.rowcount))
cursor.close
cnx.commit()
def _test_execute_cleanup(self, connection, tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = connection.cursor()
cursor.execute(stmt_drop)
except (StandardError), e:
self.fail("Failed cleaning up test table; %s" % e)
def create_command_id():
command_id = 'CMD_'
sql = 'select count(*)+1 as count ,date_format(now(),\'%Y_%m_%d_%H_%i_%s\') as c_time from queue_messages'
cursor = cnx.cursor(dictionary=True)
cursor.execute(sql)
for row in cursor:
command_id = command_id + str(row['c_time'])+'_'+str(row['count'])
print ('Command ID - '+command_id)
return command_id
def countBySql(self,sql,params = {},join = 'AND'):
# 自定义sql 统计影响行数
try:
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
except:
raise BaseError(707)
def _test_execute_cleanup(self,db,tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = db.cursor()
cursor.execute(stmt_drop)
except (Exception) as e:
self.fail("Failed cleaning up test table; %s" % e)
cursor.close()
def _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS %s"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop % sp_name)
except errors.Error, e:
self.fail("Failed cleaning up test stored routine; %s" % e)
def _test_callproc_cleanup(self,db,prc="myconnpy_callproc"):
sp_names = ('myconnpy_sp_1','myconnpy_sp_2')
stmt_drop = "DROP PROCEDURE IF EXISTS %s"
try:
cursor = db.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop % sp_name)
except errors.Error as e:
self.fail("Failed cleaning up test stored routine; %s" % e)
cursor.close()
def _test_execute_setup(self,db,tbl="myconnpy_cursor",engine="MyISAM"):
self._test_execute_cleanup(db,tbl)
stmt_create = """CREATE TABLE %s
(col1 INT, col2 VARCHAR(30), PRIMARY KEY (col1))
ENGINE=%s""" % (tbl,engine)
try:
cursor = db.cursor()
cursor.execute(stmt_create)
except (StandardError), e:
self.fail("Failed setting up test table; %s" % e)
def view_sales_spec_year(self):
## get sql
screen = self._Menu_Node__tree._Menu_Tree__screen
screen.output("Enter Year")
while True:
screen.update()
ans = screen.input()
if not ans.isdecimal(): continue
year = int(ans)
break
cursor.execute(sql, (year, ))
interface.table.table(cursor, screen)
def _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS {procname}"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop.format(procname=sp_name))
except errors.Error as err:
self.fail("Failed cleaning up test stored routine; {0}".format(err))
cursor.close()
def get_last_update_row_id():
last_update_row_id = 0
try:
select_sql = 'select row_id from maticagent_replicate.replication_controller where id in (select max(id) from maticagent_replicate.replication_controller)'
print select_sql
cursor = cnx.cursor()
cursor.execute(select_sql)
for row in cursor:
last_update_row_id = row[0]
except mysql.connector.Error as err:
print(err)
cursor.close();
return last_update_row_id
def _test_callproc_setup(self, connection):
self._test_callproc_cleanup(connection)
stmt_create1 = (
"CREATE PROCEDURE myconnpy_sp_1"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SET pProd := pFac1 * pFac2; END;")
stmt_create2 = (
"CREATE PROCEDURE myconnpy_sp_2"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2; END;"
)
stmt_create3 = (
"CREATE PROCEDURE myconnpy_sp_3"
"(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
stmt_create4 = (
"CREATE PROCEDURE myconnpy_sp_4"
"(IN pStr1 VARCHAR(20), INOUT pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
try:
cursor = connection.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
cursor.execute(stmt_create3)
cursor.execute(stmt_create4)
except errors.Error as err:
self.fail("Failed setting up test stored routine; {0}".format(err))
cursor.close()
def newrequest(cnx, cursor, id, placeid, which):
if which == 0:
start = input('If there is a start address, input it. Otherwise press enter\n')
end = input('If there is an end address, input it. Otherwise press enter\n')
desc = input('Please input the bike feature description:\n')
if start.isdigit():
if end.isdigit():
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, startaddress, endaddress, description) "
"VALUES (%s, %s, %s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, start, end, desc))
else:
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, startaddress,description) "
"VALUES (%s, %s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, start, desc))
cnx.commit()
else:
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, description) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, desc))
cnx.commit()
print("Change request submitted")
elif which == 1:
desc = input('Please input the bike feature description:\n')
new = ("INSERT INTO change_requests "
"(userid, changetype, intersectionid, description) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, desc))
cnx.commit()
print("Change request submitted")
def update_status(dct_update_status):
sql = "%s%s" % ('insert into '+mysql_server_controller_schema+'.client_status (row_recieved,successfully_processed,time_stamp, status_sent,CMD_ID)',
' values (\''+str(dct_update_status['row_count'])+'\',\''+str(dct_update_status['rows_processed'])+'\',now(),\'P\',\''+element_map['CID']+'\')')
print sql;
try:
cursor = cnx.cursor()
cursor.execute(sql)
cnx.commit()
except mysql.connector.Error as err:
print err
cid = element_map['CID']
update_origin_server(cid)
return 0
def deleteByAttr(self,table,params={},join='AND'):
# 删除数据
try :
fields = ','.join(k+'=%s' for k in params.keys())
sql = "DELETE FROM `%s` "% table
sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
cursor.execute(sql,tuple(params.values()))
self.__conn.commit()
return cursor.rowcount
#except:
# raise BaseError(704)
except Exception as err:
raise BaseError(704,err._full_msg)
def view_sales_spec_month(self):
## get sql
screen = self._Menu_Node__tree._Menu_Tree__screen
screen.output("Enter Month and Year, Seperated By '/'")
while True:
screen.update()
ans = screen.input().split("/")
if len(ans) != 2: continue
if not ans[0].isdecimal(): continue
if not ans[1].isdecimal(): continue
if int(ans[0]) > 12: continue
month, year = int(ans[0]), int(ans[1])
break
cursor.execute(sql, (month, year))
interface.table.table(cursor, screen)
def findBySql(self,sql,params = {},limit = 0,join = 'AND',lock=False):
"""
自定义sql语句查找
limit = 是否需要返回多少行
params = dict(field=value)
join = 'AND | OR'
"""
try :
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall()
result = [dict(zip(cursor.column_names,row)) for row in rows] if rows else None
return result
except:
raise BaseError(706)
def _execute(self, cursor, query, parameters, kwparameters):
try:
return cursor.execute(query, kwparameters or parameters)
except mysql.connector.errors.OperationalError:
logging.error("Error connecting to MySQL on %s", self.host)
self.close()
raise
def adduser():
try:
insert = (request.form['username'],request.form['type'])
cursor = cnx.cursor()
querry = ("insert into users (name, type) VALUES (%s,%s)")
cursor.execute(querry, insert)
result = cursor.fetchall
logTime = datetime.now()
logUserId = result[0][0]
print(cursor.statement + " " + str(cursor.rowcount))
cursor.close()
cnx.commit()
logAction = "User " + insert[0] + "added with type " + insert[1]
logDbAction(logUserId,logAction,logTime)
return "successful"
except Exception, err:
print Exception,err
def __query(self,table,criteria,all=False,isDict=True,join='AND'):
'''
table : 表名
criteria : 查询条件dict
all : 是否返回所有数据，默认为False只返回一条数据,当为真是返回所有数据
isDict : 返回格式是否为字典，默认为True ，即字典否则为数组
'''
try :
if all is not True:
criteria['limit'] = 1 # 只输出一条
sql,params = self.__contact_sql(table,criteria,join) #拼sql及params
'''
# 当Where为多个查询条件时，拼查询条件 key 的 valuse 值
if 'where' in criteria and 'dict' in str(type(criteria['where'])) :
params = criteria['where']
#params = tuple(params.values())
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
params=tuple(whereValues)
else :
params = None
'''
#__contact_where(params,join='AND')
cursor = self.__getCursor()
self.__display_Debug_IO(sql,params) #DEBUG
#if self.DataName=="ORACLE":
#sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % ()
#pass
cursor.execute(sql,params if params else ())
rows = cursor.fetchall() if all else cursor.fetchone()
if isDict :
result = [dict(zip(cursor.column_names,row)) for row in rows] if all else dict(zip(cursor.column_names,rows)) if rows else {}
else :
result = [row for row in rows] if all else rows if rows else []
return result
except Exception as err:
try :
raise BaseError(706,err._full_msg)
except :
raise BaseError(706)
