0

I would like to parse periodical txt files into a database through SqlAlchemy and Pandas to_sql method. But i receive SqlAlchemy Error such as below, probably an SQL syntax error. Could you please tell me how can i edit the SQL query ? 

import glob
import os
import pandas as pd
from sqlalchemy import create_engine

database_username = 'user'
database_password = 'usr321'
database_ip       = '10.1.10.1'
database_name     = 'mydb'

engine = sqlalchemy.create_engine('mysql+mysqlconnector://{0}:{1}@{2}/{3}'.format(database_username, database_password,database_ip, database_name))


list_of_files = glob.glob('/data01/*.txt')

for i in list_of_files:
    data = pd.read_csv(i, header=None)
    data.to_sql(name='table3', con=engine, if_exists = 'append', index=False)

THE ERROR : 

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1054 (42S22): Unknown column '0' in 'field list'
[SQL: INSERT INTO table3(`0`, `1`, `2`, `3`, `4`, `5`, `6`) VALUES (%(0)s, %(1)s, %(2)s, %(3)s, %(4)s, %(5)s, %(6)s)]
[parameters: ({'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk01', '2': 1, '3': 1753359343, '4': 1959.0, '5': 1753359344, '6': 1961.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk02', '2': 1, '3': 1753359343, '4': 1962.0, '5': 1753359344, '6': 1963.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk03', '2': 1, '3': 1753359343, '4': 1954.0, '5': 1753359344, '6': 1954.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk01', '2': 1, '3': 1753359343, '4': 1967.0, '5': 1753359344, '6': 1967.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk02', '2': 1, '3': 1753359343, '4': 1970.0, '5': 1753359344, '6': 1972.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk03', '2': 1, '3': 1753359343, '4': 1975.0, '5': 1753359344, '6': 1976.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_mbhdra01_lnk01', '2': 1, '3': 1753359343, '4': 1438.0, '5': 1753359344, '6': 1435.0}, {'0': '2020-04-29 08:40:00', '1': 'BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_mbhdra01_lnk02', '2': 1, '3': 1753359343, '4': 1438.0, '5': 1753359344, '6': 1436.0}  ... displaying 10 of 6578 total bound parameter sets ...  {'0': '2020-04-29 08:40:00', '1': 'MBHDRA01_SPS/Diameter Link:Link name=mbhdra01_mbepgwv01_c3_lnk02', '2': 1, '3': 1753359343, '4': 1982.0, '5': 1753359344, '6': 1985.0}, {'0': '2020-04-29 08:40:00', '1': 'MBHDRA01_SPS/Diameter Link:Link name=mbhdra01_ANKSPTS03_lnk02', '2': 1, '3': 1753359343, '4': 0.0, '5': 1753359344, '6': 0.0})]
(Background on this error at: http://sqlalche.me/e/f405)

if it is needed content of each file like this ;

Content of each file like ;

[neg@nik:/data01]# head file_2020-04-29_13:55:00.txt
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk01,True,1753359343,2374.0,1753359344,2375.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk02,True,1753359343,2371.0,1753359344,2370.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bskhdra1_lnk03,True,1753359343,2374.0,1753359344,2376.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk01,True,1753359343,2377.0,1753359344,2377.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk02,True,1753359343,2369.0,1753359344,2364.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_bvhdra01_lnk03,True,1753359343,2362.0,1753359344,2361.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_mbhdra01_lnk01,True,1753359343,1629.0,1753359344,1631.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_mbhdra01_lnk02,True,1753359343,1634.0,1753359344,1632.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_mbhdra01_lnk03,True,1753359343,1633.0,1753359344,1632.0
2020-04-29 13:55:00,BSKHDEA1_SPS/Diameter Link:Link name=bskhdea1_ipx_ibasis1_asd_lnk02,True,1753359343,3871.0,1753359344,3869.0
canarcho
  • 81
  • 1
  • 2
  • 12
  • 1
    "probably an SQL syntax error"? It's telling you that there is no column called `0` in your database. Your column names in the dataframe have to match up with the column names of your SQL table – roganjosh May 03 '20 at 09:14
  • thanks @roganjosh. How can i add column names? i have no column names in my original data. – canarcho May 03 '20 at 09:22
  • Using this: https://stackoverflow.com/questions/11346283/renaming-columns-in-pandas – roganjosh May 03 '20 at 09:54

0 Answers0