ETL script in Python to load data from another server .csv file into mysql

Question

I work as a Business Analyst and new to Python. In one of my project, I want to extract data from .csv file and load that data into my MySQL DB (Staging). Can anyone guide me with a sample code and frameworks I should use?

Answer 1

If that's a properly formatted CSV file you can use the LOAD DATA INFILE MySQL command and you won't need any python. Then after it is loaded in the staging area (without processing) you can continue transforming it using sql/etl tool of choice.

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

A problem with that is that you need to add all columns but still even if you have data you don't need you might prefer to load everything in the staging.

Answer 2

Simple program to create sqllite. You can read the CSV file and use dynamic_entry to insert into your desired target table.

import sqlite3
import time
import datetime
import random

conn = sqlite3.connect('test.db')
c = conn.cursor()

def create_table():
    c.execute('create table if not exists stuffToPlot(unix REAL, datestamp TEXT, keyword TEXT, value REAL)')

def data_entry():
    c.execute("INSERT INTO stuffToPlot VALUES(1452549219,'2016-01-11 13:53:39','Python',6)")
    conn.commit()
    c.close()
    conn.close()

def dynamic_data_entry():
    unix = time.time();
    date = str(datetime.datetime.fromtimestamp(unix).strftime('%Y-%m-%d %H:%M:%S'))
    keyword = 'python'
    value = random.randrange(0,10)
    c.execute("INSERT INTO stuffToPlot(unix,datestamp,keyword,value) values(?,?,?,?)",
              (unix,date,keyword,value))
    conn.commit()

def read_from_db():
    c.execute('select * from stuffToPlot')
    #data = c.fetchall()
    #print(data)

    for row in c.fetchall():
        print(row)

read_from_db()

c.close()
conn.close()

You can iterate through the data in CSV and load into sqllite3. Please refer below link as well.

• Quick easy way to migrate SQLite3 to MySQL?