0

Hello I'm a python beginner, I want to know the steps and how to write the code to extract data from mysql DB to a csv file.

Python beginner
  • 1
  • 1
  • Welcome to StackOverflow! I'm afraid though your questions is way too broad. This community is to help you out with a specific code problem. So we would need to see what you've tried so far to solve your problem. Please have a look at the help section https://stackoverflow.com/help/dont-ask – obscure Apr 16 '19 at 18:21

1 Answers1

0

Example MySQL Database:

enter image description here

Solution:

You can use sqlalchemy with pandas to import the data into a pandas DataFrame from a mysql database.

Note: You may need to install PyMySql if sqlalchemy.read_sql() won't work.

A pandas DataFrame has method to_csv() which can be used to export the DataFrame into a CSV file.

from sqlalchemy import create_engine
from pandas import read_sql

# MySQL database connection
engine = create_engine("mysql+pymysql://%s:%s@localhost:3306/%s"
                       %('username','password','dbname'),echo=False)
source_connection = engine.connect()

# Read the data from the database connection into a pandas DataFrame
df = read_sql('SELECT * FROM test', con=source_connection)

df.to_csv('/users/testuser/myfile.csv')

This creates a csv file:

enter image description here

Note: DataFrame.to_csv() has several options for formatting the data being exported. See documentation here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html

ALTERNATIVE ANSWER

dump csv from sqlalchemy

Tom Bailey
  • 321
  • 2
  • 11