CODEWITHSUNDEEP
pythonmysqlpandasread-sql
Asad Khalil
Asad Khalil

Reputation: 45

Exporting MYSQL tables to csv through python script?

Although there are many solutions to export the mysql tables to csv using python. I want to know the best way of doing that? Currently I am storing around 50 tables to csv which takes around 47 minutes and also takes more than 16gb of memory.

The code is :

sqlEngine = create_engine(f'mysql+pymysql://{MYSQL_READER_USERNAME}:%s@{MYSQL_READER_HOST}/{MYSQL_DB_NAME}' % urllib.parse.quote(f'{MYSQL_READER_PASSWORD}'), pool_recycle=3600)

def export_table(name, download_location):
    table = pd.read_sql(f'select /*+ MAX_EXECUTION_TIME(100000000) */ * from {name}', sqlEngine)
    table.to_csv(os.path.join(download_location, name + '.csv'), index=False)

tables = ['table1', ... , 'table50']

for table in tqdm(tables):
    print(f'\t => \t Storing {table}')
    export_table(table, store_dir)

I have seen many methods to store to csv like:

Is there any other method or technique and which one is best to reduce memory or execution time ?

Upvotes: 0

Views: 595

Answers (0)

Related Questions