Python insert tuple into mysql table

Question

I'm trying to insert this tuple into a mysql table.

extract of the tuple:

('2022-06-29 04:50:00', 'var1', 'var2', 'value'), ('2022-06-29 10:58:00', 'var1', 'var2', 'value'), ('2022-06-29 10:59:00', 'var1', 'var2', 'value'), ('2022-06-29 11:01:00', 'var1', 'var2', 'value'),...

This is my code:

    import MySQLdb
    
    connexion = MySQLdb.connect(host="127.0.0.1", user="myuser", password="mypass", database="mydb")
    cursor = connexion.cursor()
    table = mytablename
    
    cursor.execute("""insert ignore into " + table  + "(collection_date, var1, var2, value) values ('%s', '%s', '%s', '%s')""".format(",".join(str(i) for i in tuples)))
    connexion.commit()

But i got this error:

MySQLdb._exceptions.ProgrammingError: (1064, '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 \'" + table  + "(collection_date, var1, var2, value) values (\'%s\', \'%s\', \'%s\', \'\' at line 1')

Many thanks for any help,

BR,

Pompedup · Accepted Answer · 2022-11-20T21:38:12.213

-1

You forget some " here: into " and here "(collection_date so you have into " + table + "(collection_date and not into your_table_name(collection_date

And at the end, the format doesn't work, you should concatenate your join

I let you check

tuples = ('2022-06-29 04:50:00', 'var1', 'var2', 'value'), ('2022-06-29 10:58:00', 'var1', 'var2', 'value'), ('2022-06-29 10:59:00', 'var1', 'var2', 'value'), ('2022-06-29 11:01:00', 'var1', 'var2', 'value')
table = "mytablename"

print("""insert ignore into """ + table  + """(collection_date, var1, var2, value) values """ +  ",".join(str(i) for i in tuples))

Edit

Looking a bit more thanks to Matthias' comment, the correct way looks like this to avoid sql injections. It's more secure

cursor.executemany("insert ignore into " + table  + "(collection_date, var1, var2, value) values ('%s', '%s', '%s', '%s')", tuples)

edited Nov 20 '22 at 21:38

answered Nov 20 '22 at 20:30

Pompedup

566
2
8

ho, very sorry this stuping thing.. bigger it is, many thanks for your help Pompedup – Indi59 Nov 20 '22 at 21:07
No problem, it happens to all of us! – Pompedup Nov 20 '22 at 21:16
2

You forgot to mention that you never never never should construct a query like this, if you don't want to make SQL injection possible. Use a parameterized query instead. That's what the second parameter of the `execute` method is for. – Matthias Nov 20 '22 at 21:29

Python insert tuple into mysql table

1 Answers1