Convert CSV file to the MySQL dump

CSV2DB

To convert datasets CSV files to the SQL dump, you can use mysqlimport, LOAD DATA INFILE MySQL command or pandas to_sql, if you want to do it by yourself.

But on the other hand, we've developed the csv2db tool based on pandas to_sql command. This tool will load a CSV file as a pandas Dataframe, do some basic preprocessing on the Dataframe, and then load Dataframe content with your desired columns into the MySQL database. In this document, we're going to learn how to use this tool.

---

Warnings:

Before doing anything, Please backup your database. This instruction contains manipulating the database, and this may cause some loss. We will not be responsible for any data loss from your database.

---

Parameters:

• -pa, --path : str, required

  Path of the CSV file.

• -db, --database : str, required

  Database name

• -t, --table : str, required

  Table name

• -ho, --host : str, optional, default='127.0.0.1'

  Host IP address

• -p, --port : str, optional, default='3306'

  Port of MySQL

• -u, --user : str, optional, default='root'

  MySQL user

• -ps, --password : str, required

  MySQL password

• -c, --columns : str, optional, default=None

  Array of desired columns names. Default is all columns.

---

Example command

• Load drugs_dataset.csv to the MySQL database

  python main.py --path datasets/drugs_dataset.csv --password ${MySQL_Password} --database datasets --table drugs

---

Create a dump from MySQL server

After you load your CSV file into the database, you need to get a dump from the database:

mysqldump -u root -p"$MySQL_Password" database_name > databases.sql

for example:

mysqldump -u root -p"$MySQL_Password" datasets > drugs_dataset.sql

Note: In the case of running MySQL server in docker, use should use the following command:

docker exec mysql-container sh -c 'exec mysqldump -u root -p"$MySQL_Password" database_name' > databases.sql