Warning: Incorrect Integer Value error MYSQL

Question

I am attempting to load csv files into a table in a database using python.

csv_data = csv.reader(file('ppi.csv'))

for row in csv_data:
    cursor.execute('INSERT INTO ADKnowledgeBase.PPI(a,b) VALUES("row[0]","row[1]");')

Whenever I run this, I get a warning stating Incorrect Integer Value and inserts 0's into my table.

When I print row I get [int0,int1] and they are ints so it's pushing in the ints and not null values.

I've attempted to remove the quotes around "row[0]" and "row[1] and instead use VALUES(row[0],row[1]); but that just gives me a syntax error. I've also attempted using "%d" and %d

My table was made using:

CREATE TABLE IF NOT EXISTS ADKnowledgeBase.PPI(a int, b int);

`"row[0]"` is not any integer I've heard of. (Hint: Use placeholder values) — tadman, Apr 02 '18 at 23:13
Possible duplicate of [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) — Ctznkane525, Apr 02 '18 at 23:15
@tadman I tried using %s and %d but i encounter the same issue — SemicolonExpected, Apr 02 '18 at 23:15
@mcv Yes `cursor.execute('INSERT INTO ADKnowledgeBase.PPI(a,b) VALUES("%d","%d");',(row[0],row[1]))` and my error is something like `Warning: Incorrect integer value: ''19217'' for column 'a' at row 1` I have also tried changing the datatype to a varchar in my table and instead of %d to do %s and I still have the issue — SemicolonExpected, Apr 02 '18 at 23:31

score 1 · Answer 1 · answered Apr 03 '18 at 00:30

1

So python mysql format query string always uses %s

cursor.execute("INSERT INTO ADKnowledgeBase.PPI(a,b) VALUES(%s,%s)", (row[0],row[1]))

answered Apr 03 '18 at 00:30

mcv

1,380
3
16
41

Warning: Incorrect Integer Value error MYSQL

1 Answers1