Example #1
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)
Example #2
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")
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")
Example #4
0
def get_book():
    """ Connect to MySQL database """
    conn = None
    try:
        conn = mysql.connector.connect(host='db',
                                       port='3306',
                                       database='bookflix',
                                       user='******',
                                       password='******')

        category = request.values.get('category')
        category_type = request.values.get('category_type')
        sql_select_booking_query = """Select * from book where genre = %s"""
        cursor = conn.cursor()
        cursor.execute(sql_select_booking_query, (category_type, ))
        record = cursor.fetchall()
        conn.commit()

    except Error as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
    return json.dumps(record)
Example #5
0
def login_db_con() -> 'html':
    u_id = request.form["user_id"]
    u_pw = request.form["user_pw"]
    dbconfig = {
        'host': 'localhost',
        'user': '******',
        'password': '',
        'database': 'member_db'
    }
    conn = mysql.connector.connect(**dbconfig)
    cursor = conn.cursor()
    SQL = "SELECT * FROM login_t WHERE id=%s and pw=%s"
    cursor.execute(SQL, (u_id, u_pw))
    alldata = cursor.fetchall()
    cursor.close()
    conn.close()
    info = ''
    if (len(alldata) >= 1):
        if u_id == 'admin':
            return redirect('/admin/')
        print("logged in...")
        session.clear()
        session["user_id"] = u_id
        return redirect('/')
    else:
        print("who are you...")
        return render_template("admin_login.html", info='다시입력해주세요.')
Example #6
0
def list_category():
    """ Connect to MySQL database """
    category_no = 0
    conn = None
    try:
        conn = mysql.connector.connect(host='db',
                                       port='3306',
                                       database='bookflix',
                                       user='******',
                                       password='******')

        category = request.values.get('category')
        cursor = conn.cursor()
        if category == "read":
            category_no = 1
        elif category == "started":
            category_no = 2
        elif category == "liked":
            category_no = 3
        elif category == "disliked":
            category_no = 4

        print(category_no)
        sql_list_query = "Select title, author_name from user_client natural join book where list_id = %s"
        cursor.execute(sql_list_query, (category_no, ))
        record = cursor.fetchall()

    except Error as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
    return json.dumps(record)
Example #7
0
def delete_book():
    """ Connect to MySQL database """
    conn = None
    try:
        conn = mysql.connector.connect(host='db',
                                       port='3306',
                                       database='bookflix',
                                       user='******',
                                       password='******')

        book_id = request.values.get('book_id')
        sql_select_booking_query = "Select * from book where book_id = %s"
        cursor = conn.cursor()
        cursor.execute(sql_select_booking_query, (book_id, ))
        exits = cursor.fetchone()
        if exits != "":
            sql_Delete_query = "Delete from book where book_id = %s"
            cursor = conn.cursor()
            cursor.execute(sql_Delete_query, (book_id, ))
            conn.commit()

    except Error as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
        return ""
Example #8
0
 def update(self, book):
     cursor = self.db.cursor()
     sql = "update books set title = %s, author = %s, price = %s where ISBN = %s"
     values = [book['title'], book['author'], book['price'], book['ISBN']]
     cursor.execute(sql, values)
     self.db.commit()
     return book
Example #9
0
 def create(self, book):
     cursor = self.db.cursor()
     sql = "insert into books (ISBN, title, author, price) values (%s,%s,%s,%s)"
     values = [book['ISBN'], book['title'], book['author'], book['price']]
     cursor.execute(sql, values)
     self.db.commit()
     return cursor.lastrowid
Example #10
0
 def findById(self, ISBN):
     cursor = self.db.cursor()
     sql = 'select * from books where ISBN = %s'
     values = [ISBN]
     cursor.execute(sql, values)
     result = cursor.fetchone()
     return self.convertToDict(result)
 def findById(self, id):
     cursor = self.db.cursor()
     sql = "select * from coffeeconsumers where id = %s"
     values = [id]
     cursor.execute(sql, values)
     result = cursor.fetchone()
     return self.convertToDict(result)
Example #12
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
Example #13
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 create(self, values):
        cursor = self.db.cursor()
        sql = "insert into dvds (title, director, price) values (%s,%s,%s)"
        cursor.execute(sql, values)

        self.db.commit()
        return cursor.lastrowid
Example #15
0
    def get(self):
        db = mysql.connector.connect(host="localhost",
                                     user="******",
                                     password="******",
                                     database='project')

        disease = request.args.get("disease")
        print("disease names are = %s" % (disease))
        query = """
            select sName
            from symptoms
            where sId in (
	            select ds.sId
                from diseaseSymptoms ds,disease d
	            where ds.dId = d.dId
	            and dName = "%s"
            );
        """
        cursor = db.cursor()
        cursor.execute(query % (disease))
        symptoms = cursor.fetchall()
        cursor.close()
        result = []
        for i in symptoms:
            result.append(i[0])
        return result
Example #16
0
    def delete(self, ClientID):
        cursor = self.db.cursor()
        sql = 'delete from clients where ClientID = %s'
        values = [ClientID]
        cursor.execute(sql, values)

        return {}
Example #17
0
    def get(self):
        db = mysql.connector.connect(host="localhost",
                                     user="******",
                                     password="******",
                                     database='project')

        symptoms = request.args.get("symptoms")
        symptoms = symptoms.split(",")
        symptoms = ",".join(map(str, symptoms))
        print("disease are {}".format(symptoms))
        cursor = db.cursor()
        query = """
            select dName,count(*) as frequency from
            disease d,diseaseSymptoms ds,symptoms s
            where d.dId = ds.dId
            and ds.sId = s.sId
            and sName in (%s)
            group by dName
            order by frequency desc;
        """
        cursor.execute(query % (symptoms))
        record = cursor.fetchall()
        cursor.close()
        print(record)
        return record
Example #18
0
 def get(self, table):
     db = mysql.connector.connect(host="localhost",
                                  user="******",
                                  password="******",
                                  database='project')
     data = []
     #query = 'select {0} from {1} where {0} like "{2}%"'
     # this will fetch all data from given table
     query = 'select distinct {0} from {1}'
     val = request.args.get("query")
     if (table == "disease"):
         query = query.format("dName", table)
     elif (table == "symptoms"):
         query = query.format("sName", table)
     elif (table == "medicine"):
         query = query.format("mName", table)
     elif (table == "chemicals"):
         query = query.format("cName", table)
     else:
         return {"message": "an error in query parameter"}, 400
     cursor = db.cursor()
     try:
         cursor.execute(query)
     except:
         print("error ocuured while executing sql")
         cursor.close()
         return {"message": "error occured"}, 500
     result = cursor.fetchall()
     for i in result:
         data.append(i[0])
     db.close()
     return data
Example #19
0
    def get(self):
        db = mysql.connector.connect(host="localhost",
                                     user="******",
                                     password="******",
                                     database='project')

        query = """
            select mName,modeOfAdministration
            from medicine
            where mId in (
	        select similar
	        from  similarMedicine
	        where mId in ( select s.mId
				 from medicine m,similarMedicine s
                 where m.mId = s.similar
                 and m.mName = "%s"
				)
            );
        """
        name = request.args.get("medicine")
        cursor = db.cursor()
        cursor.execute(query % (name))
        result = cursor.fetchall()
        cursor.close()
        return result
Example #20
0
def read ():
    db = mysql.connector.connect(
        # host="localhost",
        # user="******",
        # password="",
        # database = "mydatabase"
        host="dz8959rne9lumkkw.chr7pe7iynqr.eu-west-1.rds.amazonaws.com",
        user="******",
        password="******",
        database="a001ta7fuectz1bw"
    )
    cursor = db.cursor()
    ## defining the Query
    query = "SELECT link FROM links"

    ## getting records from the table
    cursor.execute(query)

    ## fetching all records from the 'cursor' object
    records = cursor.fetchall()

    ## Showing the data
    links = []
    for record in records:
        links.append(record[0])

    return links
Example #21
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)
Example #22
0
def tableDemonstracoesContaveis():
    try:
        db = mysql.connector.connect(
            host='localhost',
            user='******',
            passwd='Teste3pswd!',
            auth_plugin='mysql_native_password',
            database='teste3'
        )  # o ideal seria por a senha em outro arquivo, mas tudo bem
        cursor = db.cursor()

        with db:
            createquery = """
            CREATE TABLE IF NOT EXISTS Demonstracoes_Contaveis(
            id int AUTO_INCREMENT PRIMARY KEY,
            DATA date,
            REG_ANS int(10),
            CD_CONTA_CONTABIL int,
            DESCRICAO varchar(60),
            VL_SALDO_FINAL float(9,2))
            """
            datequery = '''SELECT DATE_FORMAT(DATA, "%d/%m/%Y") FROM Demonstracoes_Contaveis'''
            cursor.execute(createquery)
            cursor.execute(datequery)

    except mysql.connector.Error as e:
        print(e)
Example #23
0
def deposit(request):
    time1 = datetime.datetime.now()
    time2 = str(time1)
    today = time2[0:19]

    currentBalance = getData(int(request.session.get('user_id', None)))
    context = {
        "message": "Connectez - vous s.v.p ",
        "error": False,
        "data": ""
    }
    cursor = connection.cursor()
    amount = int(currentBalance['account_amount']) + 500

    # Update the Amount
    cursor.execute(
        """
        UPDATE account
        SET account_amount=%s WHERE  account_user_id=%s  
    """, (amount, request.session.get('user_id', None)))

    # Update the Transactions
    cursor.execute(
        """
        INSERT INTO `mytransaction`
        SET mytransaction_user_id=%s, mytransaction_type=%s, mytransaction_amount=%s, mytransaction_description=%s, mytransaction_date=%s ,montant_restant=%s,mytransaction_montant_depot=%s  
    """, (request.session.get('user_id', None), "Credit", "500",
          "Vous avez fait un depot de 500", today, amount, 500))

    messages.add_message(request, messages.INFO,
                         "Ton Compte a ete credite de   500/-")

    return redirect('/users/dashboard')
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
Example #25
0
def link_aadhar_card(request):
    userId = request.session.get('user_id', None)
    userDetails = user.objects.get(user_id=userId)
    cursor = connection.cursor()

    context = {"fn": "update", "userdetails": userDetails}
    currentUserDetails = user.objects.get(user_id=userId)
    context[
        'sub_heading'] = "Update Details of " + currentUserDetails.user_name
    # Message according Users Role #
    if (request.method == "POST"):
        try:
            cursor.execute(
                """
                UPDATE users_user
                SET user_aadhar=%s WHERE user_id=%s
            """, (request.POST['user_aadhar'], userId))
        except Exception, e:
            return HttpResponse('Something went wrong. Error Message : ' +
                                str(e))

        context["userdetails"] = user.objects.get(user_id=userId)
        messages.add_message(request, messages.INFO,
                             "Your Aadhar Card Updated Successfully !!!")
        return render(request, 'aadhar.html', context)
Example #26
0
def list_books():
    """ Connect to MySQL database """
    conn = None
    try:
        conn = mysql.connector.connect(host='db',
                                       port='3306',
                                       database='bookflix',
                                       user='******',
                                       password='******')

        print("DA")
        sql_list_query = "Select * from book"
        cursor = conn.cursor()
        cursor.execute(sql_list_query)
        record = cursor.fetchall()
        print("DA")
        print(record)

    except Error as e:
        print(e)

    finally:
        if conn is not None and conn.is_connected():
            conn.close()
    return json.dumps(record)
Example #27
0
    def delete(self, ISBN):
        cursor = self.db.cursor()
        sql = 'delete from books where ISBN = %s'
        values = [ISBN]
        cursor.execute(sql, values)

        return {}
Example #28
0
def demonstracoes_to_sql():
    try:
        db = mysql.connector.connect(
            host='localhost',
            user='******',
            passwd='Teste3pswd!',
            auth_plugin='mysql_native_password',
            database='teste3'
        )  # o ideal seria por a senha em outro arquivo, mas tudo bem
        cursor = db.cursor()
        for nn in range(19, 21):
            nn = str(nn)
            for i in range(1, 5):
                i = str(i)
                file = f'csvs_teste3/{i}T20{nn}.csv'
                with db:
                    with open(file, newline='') as file:
                        csv_data = csv.reader(file, delimiter=';', strict=True)
                        skip = True
                        try:
                            for row in csv_data:
                                if skip:
                                    skip = False
                                    continue
                                query = f"INSERT INTO teste3.Demonstracoes_Contaveis(DATA, REG_ANS, CD_CONTA_CONTABIL, DESCRICAO, VL_SALDO_FINAL) VALUES({row[0]},{row[1]},{row[2]},{row[3]},{row[4]})",
                                cursor.execute(query)
                        except Exception:
                            print('demonstracoes_to_sql() Error')

    except mysql.connector.Error as e:
        print(e)
Example #29
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)
Example #30
0
    def create(self):
        """
		Description
 		Function To insert value in database table stocks data
		"""

        try:
            print('Enter your name:')
            name = input()
            print('Enter your address:')
            address = input()

            print(e)
            cursor = self.db.cursor()

            val = (name, address)
            cursor.execute(
                "INSERT INTO " + self.__table_name +
                " (stockname, stockprice) VALUES (%s, %s)", val)

            self.db.commit()

            print(cursor.rowcount, "record inserted.")
        except ValueError as e:
            print(e)
Example #31
0
    def update(self):
        """
		Description
 		Function To update value in database table stocks data
		"""

        try:
            print("Enter id")
            id = input()

            print('Edit name:')
            name = input()

            print('Edit price:')
            price = input()

            cursor = self.db.cursor()
            val = (name, price, id)

            cursor.execute(
                "update " + self.__table_name +
                " set stockname=%s, stockprice=%s where stockid=%s", val)

            self.db.commit()

            print(cursor.rowcount, "record update.")

        except ValueError as e:
            print(e)
Example #32
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")
Example #33
0
 def findByID(self, eeID):
     cursor = self.db.cursor()
     sql = "select * from employees where eeID = %s"
     values = [eeID]
     cursor.execute(sql, values)
     result = cursor.fetchone()
     return self.convertToDict(result)
Example #34
0
 def findById(self, codenr): # Find a whiskey by entered the codenr
     cursor = self.db.cursor()
     sql = 'select * from whiskey where codenr = %s'
     values = [ codenr ]
     cursor.execute(sql, values)
     result = cursor.fetchone()
     return self.convertToDict(result)
Example #35
0
 def delete(self, codenr): # Delete whiskey with selected codenr
    cursor = self.db.cursor()
    sql = 'delete from whiskey where codenr = %s'
    values = [codenr]
    cursor.execute(sql, values)
    
    return {}
Example #36
0
    def insert(self, id, name, location, state, distance, max_group_size, type,
               season):
        #query mysql to insert into table
        query = (
            "INSERT INTO tour "
            "(tid, name, location, state, distance, maxGroupSize, type, season)"
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
        args = (id, name, location, state, distance, max_group_size, type,
                season)

        try:
            self.db = mysql.connector.connect(user='******',
                                              database='adventure',
                                              password="******")
            cursor = self.db.cursor()
            cursor.execute(query, args)

            if cursor.lastrowid:
                print('last insert id', cursor.lastrowid)
            else:
                print("The following was added to the table: ")
                print(args)
            self.db.commit()
            self.addTour(self.db, cursor)

        except mysql.connector.Error as error:
            print("\n" + "There was an error")
            print(error)
            print('\n' + "Please type valid input" + "\n")
            self.usrInputForInsert()
Example #37
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

            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)
Example #38
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 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 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
Example #41
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()
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
Example #43
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)
Example #44
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)
Example #45
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()
Example #46
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)
Example #47
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)
Example #48
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()
Example #49
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
Example #52
0
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")
Example #53
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()
Example #54
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 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    
Example #56
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)
Example #57
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)
Example #58
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
Example #59
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)
Example #60
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