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
Exemple #2
0
 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
Exemple #4
0
 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")
Exemple #5
0
    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)
Exemple #6
0
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
Exemple #7
0
 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")
Exemple #9
0
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
Exemple #11
0
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()
Exemple #12
0
 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
Exemple #14
0
 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)
Exemple #15
0
 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()
Exemple #16
0
    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)
Exemple #17
0
    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()
Exemple #18
0
    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)
Exemple #19
0
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
Exemple #22
0
    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    
Exemple #25
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)        
Exemple #26
0
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)
Exemple #27
0
 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)
Exemple #28
0
 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
Exemple #29
0
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
Exemple #30
0
 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)