Python: Cannot format SQL query string

Question

I want to create a SQL query string and execute it from python

import mysql.connector
from mysql.connector import Error


client = mysql.connector.connect(host=sql_host,
                                 port=sql_port,
                                 database=sql_db,
                                 user=os.environ['SQLUSER'],
                                 passwd=os.environ['SQLPASS']
                                 )
try:
    a = "val1"
    b = "val2"
    cursor = client.cursor()
    query = "insert into mytable values ('{}', '{}')".format(a,b)
    print(query)
    cursor.execute(query)

except Error as e:
    print(e)

This does not give me error but at the same time, nothing gets inserted in the table. I think thats because the query string that is created, looks like

"insert into mytable values (\\'val1\\', \\'val2\\')"

I have even tried .replace('\\','') but I cannot get the \\s removed from my query string.

What am I doing wrong?

UPDATE:

Thanks @cody for your help. But now, I am getting a different error

a = 'val1'
b = 'val2'
query = "insert into mytable values (%s, %s)"
print(query)
cursor.execute(query, (a,b))
client.commit()

Now i get

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

what does this mean? I dont even have any '' in my values

EDIT

While investigating, I see that the _executed attribute of the cursor looks like this

'insert into dev_test_storage values (\\'val1\\', \\'val2\\')'

why do i still have \\ in the query which gets executed?

Here is the create table statement

CREATE TABLE IF NOT EXISTS myTable 
(
    col1 varchar(50) not null,
    col2 varchar (100) not null
);

Your code doesn't show the query being passed to the cursor, can you add the traceback? — chris
– chris, Commented Nov 22, 2018 at 0:46
You should use query parameters instead of trying to glue values into your SQL string. — khelwood
– khelwood, Commented Nov 22, 2018 at 0:48
As @Loocid said, your execute call is empty. You probably wanted execute(query) instead of just execute(). — John Gordon
– John Gordon, Commented Nov 22, 2018 at 0:52

cody · Accepted Answer · 2018-11-23 22:16:58Z

3

Look at the following example in the MySQL connector-python documentation for insert. Don't use format for your prepared statement parameters, instead pass them as the second argument to execute. The example shows passing the data both as a tuple and a dict:

from __future__ import print_function
from datetime import date, datetime, timedelta
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

tomorrow = datetime.now().date() + timedelta(days=1)

add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")
add_salary = ("INSERT INTO salaries "
              "(emp_no, salary, from_date, to_date) "
              "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

# Insert new employee
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

# Insert salary information
data_salary = {
  'emp_no': emp_no,
  'salary': 50000,
  'from_date': tomorrow,
  'to_date': date(9999, 1, 1),
}
cursor.execute(add_salary, data_salary)

# Make sure data is committed to the database
cnx.commit()

cursor.close()
cnx.close()

Additionally, you probably need to call commit as the documentation states

Since by default Connector/Python turns autocommit off, and MySQL 5.5 and higher uses transactional InnoDB tables by default, it is necessary to commit your changes using the connection's commit() method. You could also roll back using the rollback() method.

Edit:

I'm not sure why you're still having issues with the query being improperly escaped, I've replicated your conditions as closely as possible and it works fine:

Table:

MariaDB [pets]> DESCRIBE myTable;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | varchar(50)  | NO   |     | NULL    |       |
| col2  | varchar(100) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Python code:

import mysql.connector

cnx = mysql.connector.connect(user='cody', password='secret', database='pets')
cursor = cnx.cursor()

a = 'val1'
b = 'val2'

query = "insert into myTable values (%s, %s)"

cursor.execute(query, (a,b))

cnx.commit()

print(cursor._executed)

cursor.close()
cnx.close()

The program runs successfully and prints the executed query as expected:

cody@servo:~$ python mysql-test.py
insert into myTable values ('val1', 'val2')

And the row is inserted:

MariaDB [pets]> SELECT * FROM myTable;
+------+------+
| col1 | col2 |
+------+------+
| val1 | val2 |
+------+------+
1 row in set (0.01 sec)

edited Nov 23, 2018 at 22:16

answered Nov 22, 2018 at 1:17

cody

11.2k3 gold badges27 silver badges39 bronze badges

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

6 Comments

AbtPst Over a year ago

please see the latest update. I get a different error now

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

cody Over a year ago

I'm not sure what that could be, the only thing I see that's off is you should be caling commit on the connection object, not the cursor. What is the structure of mytable? Can you include the output of DESCRIBE mytable?

AbtPst Over a year ago

by the way, are you using python 3?

cody Over a year ago

I'm stumped, I've tried to replicate your conditions as closely as possible and it works, see the edits to my answer. Can you post the entirety of the program you're running?

cody Over a year ago

Yep, I'm running Python 3.5.3

|

Collectives™ on Stack Overflow

Python: Cannot format SQL query string

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related