How to execute database insert do MariaDB using Python?

Question

I have a problem inserting data into my database table. I'm using Python and MariaDB. Connection to database is open and tested, I'm able to query the database, but I can't nail the insert syntax. I've found two ways, but neither work.

insert = (
    "INSERT INTO ksiazka (ISBN, tytul, autor, rok_wydania, ilosc stron)"
    "VALUES (%s,%s,%s,%s,%s)"
)
dane = (ISBN, tytul, autor, rok_wydania, ilosc_stron)
cursor.execute(insert, dane)

or this way:

cursor.execute("INSERT INTO ksiazka (ISBN, tytul, autor, rok_wydania, ilosc stron) VALUES (%s,%s,%s,%s,%s)", (ISBN, tytul, autor, rok_wydania, ilosc_stron))

When executing I get this error:

Traceback (most recent call last): File "C:\Users\jakub\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 377, in cmd_query raw_as_string=raw_as_string) _mysql_connector.MySQLInterfaceError: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'stron)VALUES ('12345678','wertvfdg','3','1243','213')' at line 1

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "C:/Users/jakub/PycharmProjects/biblioteka/sql_connector.py", line 57, in cursor.execute(insert, dane) File "C:\Users\jakub\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\cursor_cext.py", line 264, in execute raw_as_string=self._raw_as_string) File "C:\Users\jakub\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection_cext.py", line 380, in cmd_query sqlstate=exc.sqlstate) mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'stron)VALUES ('12345678','wertvfdg','3','1243','213')' at line 1

tadman · Accepted Answer · 2018-05-17 20:59:37Z

1

If your column name has a space in it, then it needs special handling and you must escape it:

INSERT INTO ksiazka (ISBN, tytul, autor, rok_wydania, `ilosc stron`) ...

This is why spaces in column names are annoying and should be avoided.

answered May 17, 2018 at 20:59

tadman

212k23 gold badges237 silver badges266 bronze badges

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

1 Comment

HyperQBE Over a year ago

I'll be damned! There is no space in that column name, it should be ilosc_stron, I corrected and it started working. Thank you for catching that. My editor was underlining the whole statement and I didn't see that there was no _ there.

Collectives™ on Stack Overflow

How to execute database insert do MariaDB using Python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related