Passing MySQL CREATE TABLE SELECT query in Python raise syntax error

Question

I'm trying to pass a query to sql in a loop in Python:

for i in superstar.iloc[:, 0]:

    cursor = conn.cursor()

    loop_cmd = f"""
    DROP TABLE IF EXISTS superstar_fwdPlusbwd_{i}; 
    CREATE TABLE superstar_fwdPlusbwd_{i} 
    SELECT a.*, b.CitingPatPublNr as fwd_CitingPatPublNr, c.CitedPatPublNr as bwd_CitedPatPublNr 
    from orbisFirm_focals_basic_feed a 
    left join patents.Patents_ForwardCitations b on a.focal_PatPublNr = b.PatPublNr 
    left join patents.Patents_BackwardCitations c on a.focal_PatPublNr = c.PatPublNr 
    where focal_PatPublNr = "{i}"
    ;
     """
    
    cursor.execute(loop_cmd)

but this throws an syntax error:

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 'CREATE TABLE `superstar_fwdPlusbwd_AU2002310193B2`     SELECT a.*, b.CitingPatPu' at line 1")

This seems to be a syntax error inside MySql but this query worked in MySQL Workbench. Please help, thanks!

question from:https://stackoverflow.com/questions/66058404/passing-mysql-create-table-select-query-in-python-raise-syntax-error

Answer 1

You have not passed the parameter of columns in a Mysql table. You should have used the correct syntax to create a table Specify the column name, type,etc