Htykuut

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

);

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)