python - 如何从 Pandas 数据框在 MySQL 数据库中创建新表
问题描述
我最近从使用 SQLite 来满足我的大部分数据存储和管理需求转变为使用 MySQL。我想我终于安装了正确的库以使用 Python 3.6,但现在我无法从 MySQL 数据库中的数据框创建新表。
这是我导入的库:
import pandas as pd
import mysql.connector
from sqlalchemy import create_engine
在我的代码中,我首先从 CSV 文件创建一个数据框(这里没有问题)。
def csv_to_df(infile):
return pd.read_csv(infile)
然后我使用这个 def 函数建立到 MySQL 数据库的连接:
def mysql_connection():
user = 'root'
password = 'abc'
host = '127.0.0.1'
port = '3306'
database = 'a001_db'
engine = create_engine("mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8".format(user, password, host, port, database))
return engine
最后,我使用 pandas 函数“to_sql”在 MySQL 数据库中创建数据库表:
def df_to_mysql(df, db_tbl_name, conn=mysql_connection(), index=False):
df.to_sql(con = conn, name = db_tbl_name, if_exists='replace', index = False)
我使用这一行运行代码:
df_to_mysql(csv_to_df(r'path/to/file.csv'), 'new_database_table')
产生以下错误:
InvalidRequestError: Could not reflect: requested table(s) not available in Engine(mysql://root:***@127.0.0.1:3306/a001_db?charset=utf8): (new_database_table)
我认为这告诉我,在将数据框中的数据传递给该表之前,我必须首先在数据库中创建一个表,但我对此并不是 100% 肯定的。无论如何,我正在寻找一种在 MySQL 数据库中创建表的方法,而无需先手动创建表(我有许多 CSV,每个都有 50 多个字段,必须作为新表上传到 MySQL 数据库中)。
有什么建议么?
解决方案
我采用了上面 aws_apprentice 建议的方法,即先创建表,然后将数据写入表。
下面的代码首先从 df 自动生成一个 mysql 表(自动定义表名和数据类型),然后将 df 数据写入该表。
我必须克服一些小问题,例如:未命名的 csv 列,确定 mysql 表中每个字段的正确数据类型。
我敢肯定还有其他多种(更好的?)方法可以做到这一点,但这似乎有效。
import pandas as pd
from sqlalchemy import create_engine
infile = r'path/to/file.csv'
db = 'a001_db'
db_tbl_name = 'a001_rd004_db004'
'''
Load a csv file into a dataframe; if csv does not have headers, use the headers arg to create a list of headers; rename unnamed columns to conform to mysql column requirements
'''
def csv_to_df(infile, headers = []):
if len(headers) == 0:
df = pd.read_csv(infile)
else:
df = pd.read_csv(infile, header = None)
df.columns = headers
for r in range(10):
try:
df.rename( columns={'Unnamed: {0}'.format(r):'Unnamed{0}'.format(r)}, inplace=True )
except:
pass
return df
'''
Create a mapping of df dtypes to mysql data types (not perfect, but close enough)
'''
def dtype_mapping():
return {'object' : 'TEXT',
'int64' : 'INT',
'float64' : 'FLOAT',
'datetime64' : 'DATETIME',
'bool' : 'TINYINT',
'category' : 'TEXT',
'timedelta[ns]' : 'TEXT'}
'''
Create a sqlalchemy engine
'''
def mysql_engine(user = 'root', password = 'abc', host = '127.0.0.1', port = '3306', database = 'a001_db'):
engine = create_engine("mysql://{0}:{1}@{2}:{3}/{4}?charset=utf8".format(user, password, host, port, database))
return engine
'''
Create a mysql connection from sqlalchemy engine
'''
def mysql_conn(engine):
conn = engine.raw_connection()
return conn
'''
Create sql input for table names and types
'''
def gen_tbl_cols_sql(df):
dmap = dtype_mapping()
sql = "pi_db_uid INT AUTO_INCREMENT PRIMARY KEY"
df1 = df.rename(columns = {"" : "nocolname"})
hdrs = df1.dtypes.index
hdrs_list = [(hdr, str(df1[hdr].dtype)) for hdr in hdrs]
for i, hl in enumerate(hdrs_list):
sql += " ,{0} {1}".format(hl[0], dmap[hl[1]])
return sql
'''
Create a mysql table from a df
'''
def create_mysql_tbl_schema(df, conn, db, tbl_name):
tbl_cols_sql = gen_tbl_cols_sql(df)
sql = "USE {0}; CREATE TABLE {1} ({2})".format(db, tbl_name, tbl_cols_sql)
cur = conn.cursor()
cur.execute(sql)
cur.close()
conn.commit()
'''
Write df data to newly create mysql table
'''
def df_to_mysql(df, engine, tbl_name):
df.to_sql(tbl_name, engine, if_exists='replace')
df = csv_to_df(infile)
create_mysql_tbl_schema(df, mysql_conn(mysql_engine()), db, db_tbl_name)
df_to_mysql(df, mysql_engine(), db_tbl_name)
推荐阅读
- python - 如何将 B 列转置为 DataFrame 中的 y 轴列
- objective-c - 如何在 Objective-C 应用程序中观察 NSUserDefaults 的命令行更改?
- reactjs - 使用带有 react-layout-grid 的自定义组件
- asp.net - 在 ASP.NET MVC 的“详细信息”视图中显示所有图像?
- python - 如何验证文本字段是否具有我输入的值?
- python - 为什么我的不和谐机器人创建脚本不起作用?
- unit-testing - IntelliJ 2018 中单元测试的红/绿条在哪里?
- prolog - 我无法让我的 Prolog DCG 与 atom concat 一起工作
- python-imaging-library - Pillow 中的 image.close() 未释放内存
- c - 在 C 中使用“else”的预期标识符