executing a sql query using python-CodePudding

I'm trying to create a small python app to extract data from specific table of database. The extracted rows have to be between CREATION_DATETIME specified by user.

Heres the code:

startdate = input("Prosze podac poczatek przedzialu czasowego (format RRRR-MM-DD GG:MM:SS): ")
enddate = input("Prosze podac koniec przedzialu czasowego (format RRRR-MM-DD GG:MM:SS): ")
    
query = "SELECT * FROM BRDB.RFX_IKW_MODIFY_EXEC_ORDER_CANCEL_LOG WHERE CREATION_DATETIME between '%s' and '%s' ORDER BY CREATION_DATETIME DESC;"
    
tuple1 = (startdate, enddate)
cursor.execute(*query, (tuple1,))
records = cursor.fetchall()
print("Total number of rows in table: ", cursor.rowcount)
print(records)

I'm not much of developer and I'm stuck at error "TypeError: CMySQLCursorPrepared.execute() takes from 2 to 4 positional arguments but 104 were given" in various counts, depends on how I try to modify the code.

Could you guys help me out in specyfing that query correctly? Thank you in advance.

Tried various tutorial about parametrized query but with no luck.

CodePudding user response：

You're starring the query, making it an iterable of the characters making up the string, which probably isn't what you meant (i.e., you should emove the * operator). In addition, tuple1 is already a tuple, you shouldn't enclose it inside another tuple:

cursor.execute(query, tuple1)
# Remove the *-^
# Use tuple1 directly-^

CodePudding user response：

here is the full code

 import mysql.connector
    from mysql.connector import Error
    
    try:
        print("Laczenie z baza danych....")
        connection = mysql.connector.connect(host='',
                                             port='',
                                             database='',
                                             user='',
                                             password='')
        if connection.is_connected():
            db_Info = connection.get_server_info()
            print("Wersja servera MySQL:", db_Info)
            cursor = connection.cursor(prepared=True)
            cursor.execute("select database();")
            record = cursor.fetchone()
            print("Pomyslnie polaczono z baza danych: ", record)
            
    except Error as e:
        print("Blad polaczenia!", e)
        quit()
    
    try:
        startdate = input("Prosze podac poczatek przedzialu czasowego (format RRRR-MM-DD GG:MM:SS): ")
        enddate = input("Prosze podac koniec przedzialu czasowego (format RRRR-MM-DD GG:MM:SS): ")
        
        query = "SELECT * FROM BRDB.RFX_IKW_MODIFY_EXEC_ORDER_CANCEL_LOG WHERE CREATION_DATETIME between '%s' and '%s' ORDER BY CREATION_DATETIME DESC;"
        
        tuple1 = (startdate, enddate,)
        cursor.execute(query, tuple1)
        records = cursor.fetchall()
        
        print("Fetching each row using column name")
        for row in records:
            message_id = row["MESSAGE_ID"]
            executable_order_id = row["EXECUTABLE_ORDER_ID"]
            creation_datetime = row["CREATION_DATETIME"]
            message_type = row["MESSAGE_TYPE"]
            message_status = row["MESSAGE_STATUS"]
            print(message_id, executable_order_id, creation_datetime, message_status)
        
    except mysql.connector.Error as e:
        print("Error reading data from MySQL table", e)
    
    
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")