Insert into (values) from (LOOPING VARIABLE) using sqlalchemy and python-CodePudding

I am trying to insert values name and dirname into mysql table using sqlalchemy. The value is the variable I fetch from previous looping function.

My code is:

#loop function to get the value
for filenames in [file for file in inbound_files if file.endswith('.json')]:
    jobname ="Activity A"

    dirname_tup =  [x for x in source_folder.split("/") if x != ''][-1].upper(), 
    #convert tuple to str
    def convertTuple(tup):
        str = ''.join(tup)
        return str

    dirname = convertTuple(dirname_tup)

    sql = f'''
insert into table
(jobname,directoryname)
values 
'{jobName}','{dirname} as directoryname ';
'''
    updateSQL = pd.read_sql(sql,my_conn)

However I received the following error:

sqlalchemy.exc.ProgrammingError: (pymysql.err.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 ''Activity A','A_2022' as directoryname' at line 4")
[SQL: 
insert into LISTING_AUDIT_CHECK
(jobname,directoryname)
values 
'Activity A','A_2022' as directoryname;
]

I assumed I have wrongly create the sql statement using sqlalchemy. Appreciate any idea to resolve the issue.

CodePudding user response：

an INSERt has no alias, so it produces an error. further the one INSERT mathod

INSERT INTO  tablenames VALUES ('col11','col2')

The values need parenthesis

#loop function to get the value
for filenames in [file for file in inbound_files if file.endswith('.json')]:
    jobname ="Activity A"

    dirname_tup =  [x for x in source_folder.split("/") if x != ''][-1].upper(), 
    #convert tuple to str
    def convertTuple(tup):
        str = ''.join(tup)
        return str

    dirname = convertTuple(dirname_tup)

    sql = f'''
insert into table
(jobname,directoryname)
values 
('{jobName}','{dirname}');
'''
    updateSQL = pd.read_sql(sql,my_conn)