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

Question

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.

I've tried ('{jobname}','{dirname}' as directoryname); .and received error "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 'as dirname)' .. how do i create an alias for the column dirname here? another try, ('{jobname}','{dirname} as directoryname'); , returns error .. sqlalchemy.exc.ResourceClosedError: This result object does not return rows. It has been closed automatically. — ross k
– ross k, Commented Oct 15, 2022 at 22:32

nbk · Accepted Answer · 2022-10-15 23:17:42Z

1

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)

answered Oct 15, 2022 at 23:17

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related