Python MySQL syntax error while executing creatable ... like

Question

I was making backup system for my MySQL databse. Excatly then user insert new excel file old file is coped to a different name. That is part of code:

now = datetime.now()
dt_string = now.strftime("-%Y_%m_%d-%H_%M_%S")
table_name = table + dt_string
print(dt_string)
print(table)
print(table_name)
self.operator.execute("create table %s like %s", (table_name, table))
self.operator.execute("insert into %s select * from %s", (table_name, table))

Output and error code:

_2021_07_29_12_24_12
import_test_1
import_test_1_2021_07_29_12_24_12
Traceback (most recent call last):
  File "C:\Users\c1v\PycharmProjects\Python_Exel\PhoneExel\import_tester.py", line 7, in <module>
    c.import_excel("import_test_1", "C:\\Users\\c1v\\Desktop\\cats.xls")
  File "C:\Users\c1v\PycharmProjects\Python_Exel\PhoneExel\db_manager.py", line 85, in import_excel
    self.operator.execute("create table %s like %s", (table_name, table))
  File "C:\Users\c1v\PycharmProjects\Python_Exel\kivy_venv\lib\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\c1v\PycharmProjects\Python_Exel\kivy_venv\lib\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\c1v\PycharmProjects\Python_Exel\kivy_venv\lib\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 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 ''import_test_1_2021_07_29_12_24_12' like 'import_test_1'' at line 1

Cesc · Accepted Answer · 2021-07-29T13:32:38.463

0

String parameters in a sql query should be in quoted form:

.... '%s' like '%s'.....

UPDATE

In your case, you can try this:

("""create table '%s' like '%s'""", (table_name, table))

or another older solution would be :

("create table " + table_name  + " like " + table + " ")

edited Jul 29 '21 at 13:32

answered Jul 29 '21 at 10:52

Cesc

274
2
14

Did I do that correct? `("create table '%s' like '%s'", (table_name, table))` Now it gives: `mysql.connector.errors.ProgrammingError: 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 '''import_test_1-2021_07_29-15_09_15'' like ''import_test_1''' at line 1` This error is a little diffrent, but still some syntax is incorect. – noone_interesting Jul 29 '21 at 13:11
Add, is this correct? `excel_file.to_excel("'%s'", file_loc, index=False)` I mean, I anter file_loc variable, but there is index=False that is important too, and I am not sure but I think this will colidate with the Amount of arguments in class. I am using pandas for export MySQL DB to excel file. – noone_interesting Jul 29 '21 at 13:19
@noone_interesting Okey, in your case, you have to follow syntax rules for this case. I updated my answer. FYI: [link](https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/) . – Cesc Jul 29 '21 at 13:35
None of those solution works. I copied this text and it didn't work. Any other solution? – noone_interesting Jul 29 '21 at 16:36
I found this `("INSERT INTO Person (name, age) VALUES (%s, %s)", (name, age))`, but I have completly no idea how to implement that if calues are not next to each others. – noone_interesting Jul 29 '21 at 16:48
@noone_interesting I don't understand. What you put here is an insert command with syntax to indicate which columns you will insert values into. But the question is that you won't even execute the `create` command. You cannot handle the `insert` statement if you have not yet created a table. – Cesc Jul 30 '21 at 05:13
I found this way to duplicate tables with data, but I need a way to change table depending on time and user input. https://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indices-and-data – noone_interesting Jul 30 '21 at 06:23
I found solution: `self.operator.execute(f"CREATE TABLE `{table_name}` LIKE `{table}`")`, this update help me find the answer. =) Code is contain "`" character so text formating is little buggy. I want give you upvote, but I can't. But it saved for future. – noone_interesting Jul 30 '21 at 09:45

Python MySQL syntax error while executing creatable ... like

1 Answers1