使用sqlalchemy和python从(LOOPING VARIABLE)插入(值)

Question

我正在尝试使用sqlalchemy将值name和dirname插入到mysql表中。该值是我从上一个循环函数中获取的变量。

我的代码是：

#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)

然而，我收到了以下错误：

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;
]

我认为我使用sqlalchemy错误地创建了sql语句。感谢任何解决问题的想法。

Answer 1

INSERt没有别名，因此会产生错误。再加上一个INSERT mathod

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

值需要括号

#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)