Home > other >  Insert into (values) from (LOOPING VARIABLE) using sqlalchemy and python
Insert into (values) from (LOOPING VARIABLE) using sqlalchemy and python

Time:10-16

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)
  • Related