CODEWITHSUNDEEP
pythonmysqlpymysql
John
John

Reputation: 155

insert into mysql database with pymysql failing to insert

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.

Upvotes: 0

Views: 1849

Answers (4)

ExtractTable.com
ExtractTable.com

Reputation: 811

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'])

Upvotes: 0

Markus
Markus

Reputation: 3148

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.

Upvotes: 0

Diego M. Rodrigues
Diego M. Rodrigues

Reputation: 64

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')

Upvotes: 2

James Bennett
James Bennett

Reputation: 11163

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.

Upvotes: 0

Related Questions