How can i use Pandas to push periodic txt data into Mysql?

Question

I have periodic txt files in a directory ; and each file content is a timeseries data, i am trying to find ways to push this data into MySQL. I am trying to iterate through the files and put into a MySQL db but i receive an error probably it doesnt like list_of_files file type as a string, not a json, etc. How can i correct this ?

Or you can give me different suggestions if you have. Thanks.

My directory ;

...
Apr 29 12:51 file_2020-04-29_12:45:00.txt
Apr 29 12:55 file_2020-04-29_12:50:00.txt
Apr 29 13:01 file_2020-04-29_12:55:00.txt
Apr 29 13:06 file_2020-04-29_13:00:00.txt
Apr 29 13:11 file_2020-04-29_13:05:00.txt
Apr 29 13:16 file_2020-04-29_13:10:00.txt
Apr 29 13:21 file_2020-04-29_13:15:00.txt
Apr 29 13:26 file_2020-04-29_13:20:00.txt
Apr 29 13:31 file_2020-04-29_13:25:00.txt
Apr 29 13:36 file_2020-04-29_13:30:00.txt
Apr 29 13:41 file_2020-04-29_13:35:00.txt
Apr 29 13:46 file_2020-04-29_13:40:00.txt
Apr 29 13:51 file_2020-04-29_13:45:00.txt
Apr 29 13:56 file_2020-04-29_13:50:00.txt

Content of each file like ;

[neg@nik:/cnp-cnx]# 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

The method i tried ;

import mysql.connector
import glob
import os
import pandas as pd

list_of_files = glob.glob('/cnp-cnx*.txt')

cnx = mysql.connector.connect(user='user', password='password',host='10.10.1.10',database='database')
cur = cnx.cursor(buffered=True)

for i in list_of_files:
  data = pd.read_csv(i, header=None)
  cur.execute("""INSERT INTO table1 (DATETIME, NODE, COND, COUNTER1, C1753359343, COUNTER2, C1753359344) VALUES ( %s,%s,%s,%s,'%s',%s,'%s' )""", ( data[0].values, data[1].values, data[2].values,data[3].values, data[4].values, data[5].values,data[6].values ) )


>>> for i in list_of_files:
...   data = pd.read_csv(i, header=None)
...   cur.execute("""INSERT INTO table1 (DATETIME, NODE, COND, COUNTER1, C1753359343, COUNTER2, C1753359344) VALUES ( %s,%s,%s,%s,'%s',%s,'%s' )""", ( data[0].values, data[1].values, data[2].values,data[3].values, data[4].values, data[5].values,data[6].values ) )
... 
Traceback (most recent call last):
  File "<stdin>", line 3, in <module>
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/mysql/connector/cursor_cext.py", line 248, in execute
    prepared = self._cnx.prepare_for_mysql(params)
  File "/opt/rh/rh-python36/root/usr/lib64/python3.6/site-packages/mysql/connector/connection_cext.py", line 626, in prepare_for_mysql
    result = self._cmysql.convert_to_mysql(*params)
_mysql_connector.MySQLInterfaceError: Python type numpy.ndarray cannot be converted

Schnittlauch · Accepted Answer · 2020-05-08T09:54:39.360

1

You can try the pandas-function to_sql with parameter if_exists = 'append':

for i in list_of_files:
    data = pd.read_csv(i, header=None)
    data = data[["DATETIME", "NODE", "COND", "COUNTER1", "C1753359343", "COUNTER2", "C1753359344"]]
    data.to_sql(name='table1', con=engine, if_exists = 'append', index=False)

check out here how to set up the engine object: Writing to MySQL database with pandas using SQLAlchemy, to_sql

edited May 08 '20 at 09:54

answered May 01 '20 at 09:58

Schnittlauch

76
3

thanks. but is the way of this parsing correct way? i get key error ; data = data["DATETIME", "NODE", "COND", "COUNTER1", "C1753359343", "COUNTER2", "C1753359344"] – canarcho May 01 '20 at 16:47
Sorry, I did a mistake in my code. To select multiple columns you need to use df[["a", "b", ...]] instead of df["a", "b", ...] – Schnittlauch May 08 '20 at 09:56

How can i use Pandas to push periodic txt data into Mysql?

1 Answers1