Pandas 基础 (19) - 操作数据库 (read_sql, to_sql)

pip3 install PyMySQL
pip3 install sqlalchemyCopy

import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/express')Copy

df = pd.read_sql_table('users', engine)
dfCopy

df = pd.read_sql_table('users', engine, columns=['email', 'mobile'])
dfCopy

query = '''
SELECT nick_name, email from users where id < 5
'''
df = pd.read_sql_query(query, engine)
dfCopy

df = pd.read_csv('/Users/rachel/Sites/pandas/py/pandas/21_sql/customers.csv')
dfCopy

df.rename(columns = {
    'nick_name': 'name',
    'Customer Phone': 'mobile',
}, inplace = True)
dfCopy

df.to_sql(
    name = 'admin_users',
    con = engine,
    index = False,
    if_exists = 'append'
)Copy

df = pd.read_sql('admin_users', engine)
dfCopy

query = '''
SELECT nick_name, email from users where id < 5
'''
df = pd.read_sql(query, engine)
dfCopy