Cannot INSERT strings with Set Clause with mariadb connector (python)

Question

I want to insert a new row in my table by using the python-mariadb connector. For that I prefer to use the SET clause. For some reason it does work if I only want to save ints (i.e y=2), but when I use a string, the following error occurs

Unknown column 'myString' in 'field list'

It seems it thinks the content of the string is a column name? Any idea how to fix that (I can do it with INSERT INTO ... VALUES ..., but I want to use the SET clause here). From my understanding, it should save both an int and a str without throwing an error Thank you.

See the code example below

def myfunction():
    x = 1
    y ='myString'
    db = connect_db()
    cur = db.cursor()
    sql = "INSERT INTO Table SET col1={}, col2={}"
    cur.execute(sql.format(x, y))
    db.commit()
    db.close()
    return

Here the MariaDB Connector, but this should be fine as it works for other db functions.

import mariadb

def connect_db():
    db = mariadb.connect(
        user="user",
        password="123",
        host="localhost",
        port=3306,
    database="DB"
    )
db.autocommit = False
return db

eshirvana · Accepted Answer · 2021-07-14 15:17:42Z

2

you are not using right syntax for insert

sql = "INSERT INTO Table (col1,col2) values({}, {})"

but if you want to update an existing row:

sql = "UPDATE Table SET col1={}, col2={} WHERE id = {}"

and probably you need a where clause

edited Jul 14, 2021 at 15:17

answered Jul 13, 2021 at 18:51

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Comments

Michael Ruth · Accepted Answer · 2021-07-13 20:00:18Z

The code in question produces the SQL statement:

INSERT INTO Table SET col1=1, col2=myString;

This is incorrect syntax, and strings must be in single-quotes:

INSERT INTO Table (col1, col2) VALUES (1, 'myString');

def myfunction():
    x = 1
    y ='myString'
    db = connect_db()
    cur = db.cursor()
    sql = "INSERT INTO Table (col1, COL2) VALUES ({}, '{}')"
    cur.execute(sql.format(x, y))
    db.commit()
    db.close()
    return

But the above is fragile. Don't use string building methods to create SQL statements, it is much better to use parameter binding.

def myfunction():
    x = 1
    y ='myString'
    db = connect_db()
    cur = db.cursor()
    sql = "INSERT INTO Table (col1, col2) VALUES (?, ?)"
    cur.execute(sql, (x, y))
    db.commit()
    db.close()
    return

The MariaDB connector documentation explains these things.

Retrieving Data

Once you have the initial code in place you can start working with the data. The first thing you should do is try to retrieve information from the database. Here is code for a query against the employees database:
cur.execute(
    "SELECT first_name,last_name FROM employees WHERE first_name=?", 
    (some_name,)) 
MariaDB Connector/Python uses prepared statements, sanitizing and inserting the values from the tuple into the position of the question marks (?). This is safer than inserting through f-strings or format specifiers when working with user provided information.

The query results are stored in a list in the cursor object. To view the results, you can loop over the cursor.

Adding Data

Using the same execute() method with an INSERT statement, you can add rows to the table.
cursor.execute(
    "INSERT INTO employees (first_name,last_name) VALUES (?, ?)", 
    (first_name, last_name))

Thank you for your explanation. My approach was not possible in the first place.

Collectives™ on Stack Overflow

Cannot INSERT strings with Set Clause with mariadb connector (python)

2 Answers 2

Comments

Retrieving Data

Adding Data

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Retrieving Data

Adding Data

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related