Python import csv to mysql

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Python script that sweeps all csv files in a directory into df and imports to mysql

toddbrannon/import_csv_to_mysql

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

readme.md

command to start the virtual environment:

command to start the pipenv shell:

command to exit the shell:

Python script sweeps folder for csv files to create df and then imports to mysql db

Masking Database Credentials in the import_df.py Code

Note the MySQL connection code refers to masked credentials in the create_engine call. Replace the values for the ‘user’, ‘pw’, and ‘db’ with your own values directly into the import_df.py file, or create a databaseconfig.py file with your MySQL credentials and import the config file into the import_df.py file to mask your credentials (the import_df.py file already contains import databaseconfig as cfg to accomplish this).

con = sq.create_engine("mysql+pymysql://:@localhost/" .format(user=cfg.mysql["user"], pw=cfg.mysql["passwd"], db=cfg.mysql["db"])) 

If you plan to create a git repo for your project, be sure to add databaseconfig.py to your .gitignore file in order not to expose your db connection credentials.

Create a databaseconfig.py in the same directory as import_df.py.

Insert the following code into databaseconfig.py, change the values according to your needs and save:

About

Python script that sweeps all csv files in a directory into df and imports to mysql

Источник

How to add data from CSV file to MySQL using Python

So, let’s get started with today’s tutorial. Make sure to have data.csv file into the same folder as your python file.
I assume that you have mysql up and running.

Step [1]: Import Required Modules

In this step I will import important modules

import pathlib import csv import mysql.connector 

Step [2]: Assign file path

Make sure to keep you csv file in current working directory

csv_path = pathlib.Path.cwd() / "data.csv" 

Step [3]: Fetch data from the csv file and add to dictionary

This will create a list with dictionaries as shown above

dict_list = list() with csv_path.open(mode="r") as csv_reader: csv_reader = csv.reader(csv_reader) for rows in csv_reader: dict_list.append() 

Step [4]: Add a MySQL connection and cursor

Using connection we connect to database and cursor will help performing operations on it

mydatabase = mysql.connector.connect( username = 'root', password = 'adminuser', host = 'localhost', ) mycursor = mydatabase.cursor() 

Step [5]: Create a database and assign it to connection

We’re making a database name «myempdb»

mycursor.execute("CREATE DATABASE myempdb") # add below line inside your connect method # database = 'myempdb', 

Step [6]: Create table ’emptable’ in myempdb

The table will contains 3 rows «empid», «name», «phone»

mycursor.execute("CREATE TABLE emptable (empid INT PRIMARY KEY, name VARCHAR(255), phone INT") 

Step [7]: Adding data to database

Add list_dict data to database using loop and closing database

for item in dict_list: sql = "INSERT INTO emptable(empid, name, phone) VALUES (%s, %s, %s)" val = item['empid'], item['name'], item['phone'] mycursor.execute(sql, val) mydatabase.commit() 

Step [8]: Fetch the data and display it on terminal

This will show all the data stored in «emptable» into terminal

mycursor.execute('SELECT * FROM emptable') myresult = mycursor.fetchall() for x in myresult: print(x) 

Step [9]: Fetch the data and display it on terminal

This will disconnect the connection to MySQL server

Final code

# fetch data from csv file import pathlib import csv import mysql.connector csv_path = pathlib.Path.cwd() / "data.csv" dict_list = list() with csv_path.open(mode="r") as csv_reader: csv_reader = csv.reader(csv_reader) for rows in csv_reader: dict_list.append() mydatabase = mysql.connector.connect( username = 'root', password = 'adminuser', host = 'localhost', database = 'myempdb', ) mycursor = mydatabase.cursor() mycursor.execute("CREATE DATABASE myempdb") mycursor.execute("CREATE TABLE emptable (empid INT PRIMARY KEY, name VARCHAR(255), phone INT") for item in dict_list: sql = "INSERT INTO emptable(empid, name, phone) VALUES (%s, %s, %s)" val = item['empid'], item['name'], item['phone'] mycursor.execute(sql, val) mydatabase.commit() mycursor.execute('SELECT * FROM emptable') myresult = mycursor.fetchall() for x in myresult: print(x) mydatabase.close() 

Источник

Python read, validate and import CSV/JSON file to MySQL

In this post how to read, parse and load CSV/JSON file to MySQL table:

• Python with Pandas and MySQL — read CSV file(pandas), connect to MySQL(sqlalchemy), create a table with CSV data
• Read, parse and load JSON file into MySQL table- Read and parse JSON, validate data, connect and insert to MySQL(PyMySQL)

Read CSV file with Pandas and MySQL

The easiest and simplest way to read CSV file in Python and to import its date into MySQL table is by using pandas. Several useful method will automate the important steps while giving you freedom for customization:

import pandas as pd from sqlalchemy import create_engine # read CSV file column_names = ['person','year', 'company'] df = pd.read_csv('/home/user/data/test.csv', header = None, names = column_names) print(df) df = pd.read_csv('/home/user/data/test.csv', header = 0) print(df) engine = create_engine('mysql://root:@localhost/test') with engine.connect() as conn, conn.begin(): df.to_sql('csv', conn, if_exists='append', index=False) 

Open CSV file with pandas

First step is to read the CSV file data. This is done by:

df = pd.read_csv('/home/user/data/test.csv', header = None, names = column_names) print(df) 

df = pd.read_csv('/home/user/data/test.csv', header = 0) print(df) 

the difference is about headers — in first code the csv files is without headers and we provide column names. The second example the file has headers and we show that line — 0 — should be used as header.

Note also that method pd.read_csv will add index or line number to your rows and the result would be:

 person year company 0 Person Year Company 1 John 2018 Google 

Connect to MySQL DB with sqlalchemy

next step is to connect to MySQL DB ( or any other — you will need to change your module)

engine = create_engine('mysql://root:@localhost/test') 

username — is the username which will connect to DB
password — the password of the user. In case of no password than you can leave it empty. So for root without password you can use: root:
localhost — the MySQL server address
dbname — the database that will be used for your connection.

create new MySQL table from the CSV data

Final step is creating new table from the CSV data:

with engine.connect() as conn, conn.begin(): df.to_sql('csv', conn, if_exists='append', index=False) 

df.to_sql(‘csv’, conn, if_exists=’append’, index=False)

• csv — is the table name which will be created in database test.
• conn — we are creating new connection with the parameters from the previous step
• if_exists — this parameter has two options
  • append — if you want to append data to existing table
  • replace — recreate the table and insert the data into newly created table
  • False — insert the CSV data as it is
  • True — this will add column ‘index’ in the field list of your CSV. So instead of having:

  INSERT INTO csv (`Person`, `Year`, `Company`) VALUES (%s, %s, %s)'] [parameters: (('John', 2018, 'Google') 

  INSERT INTO csv (`index`, `Person`, `Year`, `Company`) VALUES (%s, %s, %s, %s)'] [parameters: ((0, 'John', 2018, 'Google') 

  Import JSON file into MySQL

  If you want to read json file and parse it’s data you have several ways to do it with Python. In this tutorial we will see how to do it by using modules: pymysql, os, json. We will validate JSON data before loading it to MySQL.

  import pymysql, os, json # read JSON file which is in the next parent folder file = os.path.abspath('../../..') + "/test.json" json_data=open(file).read() json_obj = json.loads(json_data) # do validation and checks before insert def validate_string(val): if val != None: if type(val) is int: #for x in val: # print(x) return str(val).encode('utf-8') else: return val # connect to MySQL con = pymysql.connect(host = 'localhost',user = 'root',passwd = '',db = 'test') cursor = con.cursor() # parse json data to SQL insert for i, item in enumerate(json_obj): person = validate_string(item.get("person", None)) year = validate_string(item.get("year", None)) company = validate_string(item.get("company", None)) cursor.execute("INSERT INTO testp (person, year, company) VALUES (%s, %s, %s)", (person, year, company)) con.commit() con.close() 

  Read and parse JSON with JSON

  • the first step is to read and parse the JSON file into python dict. You can open the file by absolute or relative path:

  file = '/home/user/data/test.json' 

  for i, item in enumerate(json_obj): person = validate_string(item.get("person", None)) 

  The enumerate is used in order to get index — i — just in case if you want to insert a unique key to your data. At this step is invoked method validate_string to verify JSON data. You are free to add or change validation to match your needs. Useful data integrity checks are:

  * check for duplicated data * check for incomplete data - partially required fields (fields depending on other fields) * check for required fields * check for wrong types * check for bad formats - incomplete date * method validate_string - validates if the values are type string. You can write check that meet your requirments. It's better to be used is_instance rather than direct comparisson of types. 

  Connect and insert to MySQL with pymysql

  • then we are connecting to the DB. The parameters are similar to the CSV example.
  • last step is to prepare insert method and write the data into MySQL

  cursor.execute("INSERT INTO test (person, year, company) VALUES (%s, %s, %s)", (person, year, company)) 

  For JSON you can also use pandas in order to load JSON data into DB. Both ways has their advantages and disadvantages. What you are going to use depends on you.

  This is a simple example using JSON and pandas.io:

  from pandas.io.json import json_normalize data = [< "Person": "John", "Year": 2018, "Company": "Google" >, < "Person": "Sam", "Year": 2017, "Company": "IBM" >, < "Person": "Jeff", "Year": 2014, "Company": "Yahoo" >] json_normalize(data) 

  CSV file example:

  This is the example CSV file used in this example.

  Person,Year,Company
  John,2018,Google
  Sam,2017,IBM
  Jeff,2014,Yahoo
  Allen,2015,Facebook

  By using SoftHints — Python, Linux, Pandas , you agree to our Cookie Policy.

  Источник