insert into mysql database with pymysql failing to insert

Question

I'm trying to insert dummy data into a mysql database.

The database structure looks like:

database name: messaround database table name: test

table structure:

id (Primary key, auto increment) path (varchar(254))

UPDATED 2 method below, and error. I have a method to try to insert via:

def insert_into_db(dbcursor, table, *cols, **vals):
    try:
        query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))  
        print(query)
        dbcursor.execute(query)
        dbcursor.commit()
        print("inserted!")
    except pymysql.Error as exc:
        print("error inserting...\n {}".format(exc))

connection=conn_db()        
insertstmt=insert_into_db(connection, table='test', cols=['path'], vals=['test.com/test2'])

However, this is failing saying:

INSERT INTO test () VALUES ('vals'),('cols')
error inserting...
 (1136, "Column count doesn't match value count at row 1")

Can you please assist?

Thank you.

I see the path comes from 'vals' which are characters but not numbers. you should wrap the vals with quotes like "path_val" — ExtractTable.com
– ExtractTable.com, Commented May 4, 2018 at 12:43
@UdayS - can you put this into an answer so I can see more clearly please. — John
– John, Commented May 4, 2018 at 12:45

Diego M. Rodrigues · Accepted Answer · 2018-05-04 13:05:08Z

2

If you use your code:

def insert_into_db(dbcursor, table, *cols, **vals):
    query = "INSERT INTO {} ({}) VALUES ({})".format(table,",".join(cols), ",".join(vals))
    print(query)

insert_into_db('cursor_here', 'table_here', 'name', 'city', name_person='diego', city_person='Sao Paulo')

Python returns:

INSERT INTO table_here (name,city) VALUES (name_person,city_person)

Now with this other:

def new_insert_into_db(dbcursor, table, *cols, **vals):
    vals2 = ''
    for first_part, second_part in vals.items():
        vals2 += '\'' + second_part + '\','
    vals2 = vals2[:-1]
    query = "INSERT INTO {} ({}) VALUES ({})".format(table,",".join(cols), vals2)
    print(query)

new_insert_into_db('cursor_here', 'table_here', 'name', 'city', name_person='diego', city_person='Sao Paulo')

Python will return the correct SQL:

INSERT INTO table_here (name,city) VALUES ('diego','Sao Paulo')

answered May 4, 2018 at 13:05

Diego M. Rodrigues

641 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

appreciate the answer. is it possible to make this less convoluted? Like, is it possible to make it easier to read with less concatentation?

James Bennett · Accepted Answer · 2018-05-04 12:39:02Z

0

Generally in Python you pass a parameterized query to the DB driver. See this example in PyMySQL's documentation; it constructs the INSERT query with placeholder characters, then calls cursor.execute() passing the query, and a tuple of the actual values.

Using parameterized queries is also recommended for security purposes, as it defeats many common SQL injection attacks.

answered May 4, 2018 at 12:39

James Bennett

11.2k4 gold badges39 silver badges25 bronze badges

Comments

Markus · Accepted Answer · 2018-05-04 13:12:09Z

0

you should print the sql statement which you've generated, that makes it a lot easier to see what's wrong.

But I guess you need quotes ' around string values for your ",".join(vals) (in case there are string values.
So your code is producing

insert into test (path,) values (test.com/test2,);

but it should produce

insert into test (`path`) values ('test.com/test2');

Otherwise try https://github.com/markuman/MariaSQL/ which makes it super easy to insert data to MariaDB/MySQL using pymysql.

edited May 4, 2018 at 13:12

answered May 4, 2018 at 12:42

Markus

3,1781 gold badge24 silver badges29 bronze badges

8 Comments

John Over a year ago

Did you see my insertstmt ? It includes a path as a string at the end.

Markus Over a year ago

No, because vals = ['test.com/test2,'] becomes after "values ({})".format(",".join(vals)) just 'values (test.com/test2,)'. but it needs something like "'" + ",".join(vals) + "'".

John Over a year ago

Please see updated code, and output. This still isnt working.

Markus Over a year ago

I see no updated snippet. Also, the mysql error message says exactly this: > ... near 'test.com/test2,) VALUES ()' at line 1") you're missing quotes. And, at least when you're using special characters in columnnames, you need to use backticks for it.

Markus Over a year ago

gist.github.com/markuman/d620fb3130d0966307ac71ef728f9381 this is how you python string should look like in the end, but it looks like this insert into test (path,) values (test.com/test2,);

|

ExtractTable.com · Accepted Answer · 2018-05-04 13:39:15Z

0

Change your query as below

query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))

As you are using join, the variable is expected to be a list but not a string

table = 'test'
cols = ['path']
vals = ['test.com/test2', 'another.com/anothertest']


print(query)

"INSERT INTO test (path) VALUES ('test.com/test2'),('another.com/anothertest')"

Update:

def insert_into_db(dbconnection=None, table='', cols=None, vals=None):
    mycursor = dbconnection.cursor()
    if not (dbconnection and table and cols and vals):
        print('Must need all values')
        quit()
    try:
        query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))
        mycursor.execute(query)
        dbconnection.commit()
        print("inserted!")
    except pymysql.Error as exc:
        print("error inserting...\n {}".format(exc))


connection=conn_db()        

insertstmt=insert_into_db(dbconnection=connection, table='test', cols=['path'], vals=['test.com/test2'])

edited May 4, 2018 at 13:39

answered May 4, 2018 at 13:13

ExtractTable.com

82112 silver badges21 bronze badges

3 Comments

John Over a year ago

I really appreciate the answer, but how can I execute this via the variable insertstmt as I show above?

John Over a year ago

please see updated code an error for UPDATE2 above

John Over a year ago

still struggling to formulate this, please see UPDATE2 above.

Collectives™ on Stack Overflow

insert into mysql database with pymysql failing to insert

4 Answers 4

1 Comment

Comments

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related