index.md

CSV file to mysql database - using python 2.7.5

1) Make sure you have installed Development Tools

2) Check hostname is resolving if not please add the local host entry.

3) Update your system for all fixes

4) Check firewall status

5) Stop the firewall if running

6) Recheck the firewall status

7) Check the selinux status

8) If selinux status is enabled, please set in disabled in file the /etc/selinux/config

9) We are going to install the mysql community version, so please the mysql repo from http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

10) Install the mysql noarch.rpm, it will add the new repo on your system

11) Update the newly added repo and dependencies

12) Mysql installation

13) Start the mysql server

14) Check the mysql server status

15) Follow the secure installation

16) Check if you can login in mysql server with updated password

17) By default pip not included with python2.7 and CentOS Base repo.

18) Add EPEL repo to get pip, pip help to install python modules.

19) Install the pip package

20) Check Pip is working fine

21) Check pip version

22) Create a database

23) Create table and make sure you have same number of field as in CSV.

26) Install the MySQL-Python module

27) Make the CSV file, here we have replaced spaces with comma

28) Write the python script to import the data from csv to mysql - csv_mysql.py

#!/usr/bin/python
import MySQLdb
import csv

db = MySQLdb.connect(host="pydesk", # The Host
                      user="root", # username
                      passwd="x", # password
                      db="storage") # name of the data base

cursor = db.cursor()
Query = """ LOAD DATA LOCAL INFILE 'result.csv' INTO TABLE
storage FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED
BY '"' Lines terminated by '\n' IGNORE 1 LINES """

cursor.execute(Query)
db.commit()
cursor.close()

29) run the python code

30) login in mysql and check the data. sample data..!!

                                            Thank you..!!