CODEWITHSUNDEEP
pythonmysqlstring-formattingpymysql
AbtPst
AbtPst

Reputation: 8008

Python: Cannot format SQL query string

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

Upvotes: 2

Views: 4442

Answers (1)

cody
cody

Reputation: 11157

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)

Upvotes: 3

Related Questions