CODEWITHSUNDEEP
pythonmysqlsqlbash
Hafiz Muhammad Shafiq
Hafiz Muhammad Shafiq

Reputation: 8670

Get data as csv from a very large MySQL dump file

I have a MySQL dump file as .sql format. Its size is around 100GB. There are just two tables in int. I have to extract data from this file using Python or Bash. The issue is the insert statement contains all data and that line is too lengthy. Hence, normal practice cause Memory issue as that line (i.e., all data) is load in loop also.

Is there any efficient way or tool to get data as CSV?

Just a little explanation. Following line contains actual data and it is of very large size.

INSERT INTO `tblEmployee` VALUES (1,'Nirali','Upadhyay',NULL,NULL,9,'2021-02-08'),(2,'Nirali','Upadhyay',NULL,NULL,9,'2021-02-08'),(3,'Nirali','Upadhyay',NULL,NULL,9,'2021-02-08'),....

The issue is that I cannot import it into MySQL due to resources issues.

Upvotes: 1

Views: 1799

Answers (1)

The Pilot Dude
The Pilot Dude

Reputation: 2237

I'm not sure if this is what you want, but pandas has a function to turn sql into a csv. Try this:

import pandas as pd
import sqlite3

connect = sqlite3.connect("connections.db")
cursor = connect.cursor()
                    
# save sqlite table in a DataFrame
dataframe = pd.read_sql(f'SELECT * FROM table', connect)
# write DataFrame to CSV file

dataframe.to_csv("filename.csv", index = False)

connect.commit()
connect.close()

If you want to change the delimiter, you can do dataframe.to_csv("filename.csv", index = False, sep='3') and just change the '3' to your delimiter choice.

Upvotes: 1

Related Questions