WRITING PANDAS DATA TO A MYSQL TABLE

Listing 4.13 displays the contents of pandas_write_sql.py that shows you how to write data from a Pandas DataFrame into a MySQL table.

LISTING 4.13: pandas_write_sql.py

import pandas as pd

data = pd.DataFrame({
    'book_id':[12345, 12346, 12347],
    'title':['Python Programming', 'Learn MySQL', 'Data Science Cookbook'],
    'price':[29, 23, 27]
})

import mysql.connector

mydb = mysql.connector.connect (
        host="localhost",
        user="root",
        password="os1wald123",
        database = "mytools",
        auth_plugin='mysql_native_password'
       )

from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://root:os1wald123@127.0.0.1:3306/mytools',pool_recycle=3600)
dbConn = engine.connect()

# The to_sql writes data into the book_details table:
data.to_sql('book_details', engine, if_exists...