The amazing power of Python and the Pandas framework!

This code connects to a Mysql database, reads ths content of a table and imports it into a Dataframe. From there you can twist and turn the data as if it was in an Excel spreadsheet.

import mysql.connector as sql import pandas as pd
db_connection = sql.connect(host='x.y.z.w', 
    database='xxxx', user='xxxx',password='xxxx')
db_cursor = db_connection.cursor()
db_cursor.execute('SELECT * FROM trades')
table_rows = db_cursor.fetchall()
df = pd.DataFrame(table_rows) 

And another version of the same functionality:

#!/usr/bin/env python
import pandas as pd
from sqlalchemy import create_engine

# SQLAlchemy engine parameters
dbtype = "mysql"
dbconn = "mysqlconnector"
dbuser = "xxxx"
dbpass = "xxxx"
dbhost = "x.y.z.w"
dbschm = "xxxx"
dbecho = True

# Connect to the DB
engine = create_engine(f"{dbtype}+{dbconn}://{dbuser}:{dbpass}@{dbhost}/{dbschm}?charset=utf8mb4", echo=dbecho)

# Read whole table into dataframe
df = pd.read_sql_table("products", engine)

# Bonus: export whole dataframe to CSV file without row numbers
df.to_csv("products.csv", index=False)



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.