Python Forum

I tried all the possible ways to get out this error.. I still didn't understand where its going wrong, below is my code,

mysql_conn = mysql_getConn('db')
cur = mysql_conn.cursor()
cur._defer_warnings=True
mysql_conn.escape_string("")

def load_table():
    for fname in files_path:
        filename_ext = os.path.basename(fname)
        l_filename, file_extension = os.path.splitext(filename_ext)

        with open(fname,'r') as loop_files:
            csvreader = csv.reader(loop_files,delimiter='|')
            fields=next(csvreader)
            fields_1 = map(lambda x: '`'+x+'`',fields)
            f_str=",".join(fields_1)

            # find the length of the columns and add that many place hollders #
            #place_holder = ','.join(['"'+'%s'+'"'] * len(fields))
            place_holder = ','.join(['%s'] * len(fields))

            insert_into = "INSERT INTO "
            obracket ="("
            cbracket =")"
            values = "VALUES"
            semi_colon=";"

            f_insert_into = f'{insert_into}'
            f_obracket=f'{obracket}'
            f_cbracket=f'{cbracket}'
            f_values=f'{values}'
            f_semi_colon=f'{semi_colon}'
            sql_format = f_insert_into + f'{database_name}'+'.'+ f'{l_filename}' + f_obracket + f'{f_str}' +\ 
                                                                            f_cbracket
            sql_format_values = f_values + f_obracket + f'{place_holder}' +f_cbracket + f_semi_colon
            sql_format= '"' + f'{sql_format + sql_format_values}' +'"'

            t_tup =("Feb-2016","25%","10%","11%")
            print(sql_format,t_tup)
            cur.execute(sql_format,(t_tup))
            mysql_conn.commit()

In this code while cursor executing I am getting the error 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 \'"INSERT INTO tbl(column1,columns2\' at line 1')

Note: Variable t_tup I will pass it from the file dynamically, here for testing purpose I am hard coding it

Such a silly mistake I have made

Before

sql_format = "INSERT INTO " + database_name + "." + l_filename + "(" + f_str + ")"

I changed this to

f'INSERT INTO {l_filename} ({f_str}) VALUES ({place_holder})'

In my question from line 21 to 35 is a wrong way of formatting SQL query. That caused the issue, hope this might help someone.

pradeepkumarbe

pradeepkumarbe