MYSQL not accepting values from insert statement

Question

So im trying to insert values into a MYSQL database table but the following error keeps on popping up. would really appreciate some help.

This is my code which i wrote to input a value from a file and store it in a database table.

import mysql.connector
import pickle
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='PETROL',
                                         user='Sarthak',
                                         password='q1w2e3r4t5')
    cursor = connection.cursor ( )
    print(connection)
    fp1 = open ("D:/Python/petrol/pdate/pdate.txt" , "rb+")
    while True :
        try :
            pdate = pickle.load (fp1)
            cursor.execute("DROP TABLE IF EXISTS DATES")
            cursor.execute("CREATE TABLE DATES (ID INT AUTO_INCREMENT PRIMARY KEY,Date DATE)")
            cursor.execute ("INSERT INTO DATES(Date) VALUES(pdate)")
            cursor.execute("SHOW TABLES")
            cursor.commit()
        except EOFError :
            fp6.close ()
except mysql.connector.Error as error:
    print("Failed to create table in MySQL: {}".format(error))
    cursor.close()
    connection.close()

The following error keeps on popping up -:

Failed to create table in MySQL: 1054 (42S22): Unknown column 'pdate' in 'field list'

I am not able to encounter what problem is caused by the insert statement which i wrote.

`pdate` is a variable, cursor.execute ("INSERT INTO DATES(Date) VALUES('" + pdate + ")'"). You should however use parameterized queries or at the very least sanitize `pdate` so you don't end up with a sql injection vuln. — Geoffrey, Jul 22 '20 at 06:46
The driver doesn't have access to your local variables, so it doesn't know that you want the local variable `pdate` to be used in place of `pdate`. It just sees it has a column reference, but no such column is available. Read up on parameterized queries in https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html — Mark Rotteveel, Jul 22 '20 at 06:47
@Geoffrey Good that you warn against SQL injection, but then please don't demonstrate a solution that is vulnerable to SQL injection. Either show the correct solution, or don't show a solution, but please don't show unsafe solutions. — Mark Rotteveel, Jul 22 '20 at 06:48
@MarkRotteveel comments are not solutions/answers. I showed a code sample that as simply as possible shows the failure to understand how strings work as this is a fundamental concept not specific to SQL. — Geoffrey, Jul 22 '20 at 06:59
still an error like " : 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1" keeps on popping up @MarkRotteveel. I used ur method which was given in the link. — Sarthak Kashyap, Jul 22 '20 at 07:07
some modules use `?`, other `%s` - `execute ("INSERT INTO DATES(Date) VALUES(?)", (pdate,))` or `execute ("INSERT INTO DATES(Date) VALUES(%s)", (pdate,))`. But first you have to create variable with some variable `pdate = ...` — furas, Jul 22 '20 at 07:50
BTW: it is not good idea to run it in `while True` loop - it will try to `DROP` and `CREATE` it again and again and again. You should `DROP` and `CREATE` only once and evetually use `for item in pdata:` to `INSERT` every item separatelly. If you want to put many items at once then you should use `execute_many()` — furas, Jul 22 '20 at 07:53

Seyi Daniel · Answer 1 · 2020-07-22T10:20:10.027

-1

import pickle
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='PETROL',
                                         user='Sarthak',
                                         password='q1w2e3r4t5')
    cursor = connection.cursor ( )
    print(connection)
    fp1 = open ("D:/Python/petrol/pdate/pdate.txt" , "rb+")
    while True :
        try :
            pdate = pickle.load (fp1)
            cursor.execute("DROP TABLE IF EXISTS DATES")
            cursor.execute("CREATE TABLE DATES (ID INT AUTO_INCREMENT PRIMARY KEY,Date DATE)")
            query = "INSERT INTO DATES(`Date`) VALUES(%s)"
            cursor.execute (query, pdate)
            cursor.execute("SHOW TABLES")
            connection.commit() #use connection.commit instead of cursor.commit
        except EOFError :
            fp6.close ()
except mysql.connector.Error as error:
    print("Failed to create table in MySQL: {}".format(error))
    cursor.close()
    connection.close()```

edited Jul 22 '20 at 10:20

answered Jul 22 '20 at 08:11

Seyi Daniel

2,259
2
8
18

In what way is this the fix? `pdate` is a variable, it's not going to magically be replaced in the constant string... – Geoffrey Jul 22 '20 at 09:21
i have already tried this soln is of no use to me.thanks anyway – Sarthak Kashyap Jul 22 '20 at 10:45
@SarthakKashyap Why don't you print 'pdate' to see if it conforms with MySQL Date formats (https://www.w3schools.com/sql/func_mysql_date_format.asp). If it doesn't, you'll keep getting errors. In MySQL date type don't behave like VARCHAR. If you still find it challenging you may convert your Date variable type to VARCHAR (if it doesn't disrupt your structure. – Seyi Daniel Jul 22 '20 at 11:13
Or more accurately, ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601) – Geoffrey Jul 22 '20 at 11:45

score -1 · Answer 2 · answered Jul 22 '20 at 08:18

-1

Its better you write your insert statement like this query = "INSERT INTO DATES(`Date`) VALUES(%s)" cursor.execute (query, pdate)

answered Jul 22 '20 at 08:18

Seyi Daniel

2,259
2
8
18

This answer seems correct, but poorly formatted, missing an explination as to why, and what it's doing. – Geoffrey Jul 22 '20 at 09:22
Sorry about the formatting and omission of proper explanation. As long as 'pdate' remains with the double quotes, it will be difficult for python to see it as a variable, and if it were not being posted into a date type column, there would't have been an error, but all you find in that row of the database table will be a string 'pdate' instead of the date. To get around this, one needs to use the an sql statement with parameter (i.e %s). the execute the query along any parameter (this receive several data types). – Seyi Daniel Jul 22 '20 at 10:03
f-stringing the sql statement also gives an opportunity to pass pdate as a variable just like this: ```cursor.execute (f"INSERT INTO DATES(Date) VALUES({pdate})")```, i haven't tried this though. – Seyi Daniel Jul 22 '20 at 10:07
Update your answer, don't post these as comments – Geoffrey Jul 22 '20 at 10:15

MYSQL not accepting values from insert statement

2 Answers2