1

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.

Hafiz Muhammad Shafiq
  • 8,168
  • 12
  • 63
  • 121
  • 1
    An efficient tool is MySQL. See: [How to output MySQL query results in CSV format?](https://stackoverflow.com/q/356578/3776858) – Cyrus Feb 09 '21 at 11:21
  • *Is there any efficient way or tool to get data as CSV?* Immediately from .SQL, without data importing? Remove all until first `(` (inclusive), then replace all `),(` with CR, ignore all after final `);` (inclusive)... that's all. – Akina Feb 09 '21 at 11:39
  • As already mentioned, I cannot use MySQL for this. I have to parse it either via Bash or Python – Hafiz Muhammad Shafiq Feb 09 '21 at 11:56
  • This might help: [Export a MySQL Database to SQLite Database](https://stackoverflow.com/q/5164033/3776858) – Cyrus Feb 10 '21 at 21:18

1 Answers1

1

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.

The Pilot Dude
  • 2,091
  • 2
  • 6
  • 24