Python cursor.execute函数代码示例

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()