pandas DataFrame.to_sql() 用法

to_sql() 的语法如下:

# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

DataFrame.to_sql(name, con, schema=None, 
if_exists='fail', index=True, index_label=None, 
chunksize=None, dtype=None, method=None)

我们从一个简单的例子开始。在 mysql 数据库中有一个 emp_data 表,假设我们使用 pandas DataFrame ,将数据拷贝到另外一个新表 emp_backup

import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy

engine = create_engine('mysql+pymysql://user:password@localhost/stonetest?charset=utf8')
df = pd.read_sql('emp_master', engine)
df.to_sql('emp_backup', engine)

使用 mysql 的 describe 命令比较 emp_master 表和 emp_backup 表结构:

mysql> describe emp_master;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| EMP_ID         | int(11)     | NO   | PRI | NULL    |       |
| GENDER         | varchar(10) | YES  |     | NULL    |       |
| AGE            | int(11)     | YES  |     | NULL    |       |
| EMAIL          | varchar(50) | YES  |     | NULL    |       |
| PHONE_NR       | varchar(20) | YES  |     | NULL    |       |
| EDUCATION      | varchar(20) | YES  |     | NULL    |       |
| MARITAL_STAT   | varchar(20) | YES  |     | NULL    |       |
| NR_OF_CHILDREN | int(11)     | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

emp_backup 表结构:

mysql> describe emp_backup;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| index          | bigint(20) | YES  | MUL | NULL    |       |
| EMP_ID         | bigint(20) | YES  |     | NULL    |       |
| GENDER         | text       | YES  |     | NULL    |       |
| AGE            | bigint(20) | YES  |     | NULL    |       |
| EMAIL          | text       | YES  |     | NULL    |       |
| PHONE_NR       | text       | YES  |     | NULL    |       |
| EDUCATION      | text       | YES  |     | NULL    |       |
| MARITAL_STAT   | text       | YES  |     | NULL    |       |
| NR_OF_CHILDREN | bigint(20) | YES  |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

我们发现,to_sql() 并没有考虑将 emp_master 表字段的数据类型同步到目标表,而是简单的区分数字型和字符型,这是第一个问题,第二个问题呢,目标表没有 primary key。因为 pandas 定位是数据分析工具,数据源可以来自 CSV 这种文本型文件,本身是没有严格数据类型的。而且,pandas 数据 to_excel() 或者to_sql() 只是方便数据存放到不同的目的地,本身也不是一个数据库升迁工具。

但如果我们需要严格保留原表字段的数据类型,以及同步 primary key,该怎么做呢?

使用 SQL 语句来创建表结构

如果数据源本身是来自数据库,通过脚本操作是比较方便的。如果数据源是来自 CSV 之类的文本文件,可以手写 SQL 语句或者利用 pandas get_schema() 方法,如下例:

import sqlalchemy

print(pd.io.sql.get_schema(df, 'emp_backup', keys='EMP_ID', 
   dtype={'EMP_ID': sqlalchemy.types.BigInteger(),
       'GENDER': sqlalchemy.types.String(length=20),
       'AGE': sqlalchemy.types.BigInteger(),
       'EMAIL':  sqlalchemy.types.String(length=50),
       'PHONE_NR':  sqlalchemy.types.String(length=50),
       'EDUCATION':  sqlalchemy.types.String(length=50),
       'MARITAL_STAT':  sqlalchemy.types.String(length=50),
       'NR_OF_CHILDREN': sqlalchemy.types.BigInteger()
       }, con=engine))

get_schema()并不是一个公开的方法,没有文档可以查看。生成的 SQL 语句如下:

CREATE TABLE emp_backup (
        `EMP_ID` BIGINT NOT NULL AUTO_INCREMENT,
        `GENDER` VARCHAR(20),
        `AGE` BIGINT,
        `EMAIL` VARCHAR(50),
        `PHONE_NR` VARCHAR(50),
        `EDUCATION` VARCHAR(50),
        `MARITAL_STAT` VARCHAR(50),
        `NR_OF_CHILDREN` BIGINT,
        CONSTRAINT emp_pk PRIMARY KEY (`EMP_ID`)
)

to_sql() 方法使用 append 方式插入数据

to_sql() 方法的 if_exists 参数用于当目标表已经存在时的处理方式,默认是 fail,即目标表存在就失败,另外两个选项是 replace 表示替代原表,即删除再创建,append 选项仅添加数据。使用 append 可以达到目的。

import pandas as pd
from sqlalchemy import create_engine
import sqlalchemy

engine = create_engine('mysql+pymysql://user:password@localhost/stonetest?charset=utf8')
df = pd.read_sql('emp_master', engine)
# make sure emp_master_backup table has been created
# so the table schema is what we want
df.to_sql('emp_backup', engine, index=False, if_exists='append')

也可以在 to_sql() 方法中,通过 dtype 参数指定字段的类型,然后在 mysql 中 通过 alter table 命令将字段 EMP_ID 变成 primary key。

df.to_sql('emp_backup', engine, if_exists='replace', index=False,
          dtype={'EMP_ID': sqlalchemy.types.BigInteger(),
                 'GENDER': sqlalchemy.types.String(length=20),
                 'AGE': sqlalchemy.types.BigInteger(),
                 'EMAIL':  sqlalchemy.types.String(length=50),
                 'PHONE_NR':  sqlalchemy.types.String(length=50),
                 'EDUCATION':  sqlalchemy.types.String(length=50),
                 'MARITAL_STAT':  sqlalchemy.types.String(length=50),
                 'NR_OF_CHILDREN': sqlalchemy.types.BigInteger()
                 })

with engine.connect() as con:
    con.execute('ALTER TABLE emp_backup ADD PRIMARY KEY (`EMP_ID`);')

当然,如果数据源本身就是 mysql,当然不用大费周章来创建数据表的结构,直接使用 create table like xxx 就行。以下代码展示了这种用法:

import pandas as pd 
from sqlalchemy import create_engine

engine = create_engine('mysql+pymysql://user:password@localhost/stonetest?charset=utf8')
df = pd.read_sql('emp_master', engine)

# Copy table structure
with engine.connect() as con:
    con.execute('DROP TABLE if exists emp_backup')
    con.execute('CREATE TABLE emp_backup LIKE emp_master;')

df.to_sql('emp_backup', engine, index=False, if_exists='append')
  • 25
    点赞
  • 127
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
当你使用pandas.to_sql函数将数据写入SQLite数据库时,可能会遇到“datatype mismatch”错误。这通常是由于pandas自动推断的数据类型与SQLite数据库中的数据类型不匹配导致的。可以尝试以下几种方法解决这个问题: 1. 指定数据类型:可以在to_sql函数中指定数据类型,以确保与数据库中的数据类型匹配。例如: ```python import sqlite3 import pandas as pd conn = sqlite3.connect('example.db') df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']}) df.to_sql('mytable', conn, if_exists='replace', index=False, dtype={'col1': 'INTEGER', 'col2': 'TEXT'}) ``` 在这个例子中,我们通过dtype参数指定了每列的数据类型,确保它们与数据库中的数据类型匹配。 2. 先创建表结构:在使用to_sql函数之前,可以先手动创建表结构,以确保每列的数据类型与数据库中的数据类型匹配。例如: ```python import sqlite3 import pandas as pd conn = sqlite3.connect('example.db') c = conn.cursor() c.execute('CREATE TABLE mytable (col1 INTEGER, col2 TEXT)') conn.commit() df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']}) df.to_sql('mytable', conn, if_exists='replace', index=False) ``` 在这个例子中,我们先创建了一个名为mytable的表,然后使用to_sql函数将数据写入该表中。由于表结构已经存在,to_sql函数将不再尝试自动推断数据类型,而是直接将数据写入表中。 3. 手动转换数据类型:如果你无法指定数据类型或创建表结构,可以尝试手动将数据类型转换为与数据库中的数据类型相匹配。例如: ```python import sqlite3 import pandas as pd conn = sqlite3.connect('example.db') df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']}) # 将col1转换为整数类型,col2转换为字符串类型 df['col1'] = df['col1'].astype(int) df['col2'] = df['col2'].astype(str) df.to_sql('mytable', conn, if_exists='replace', index=False) ``` 在这个例子中,我们通过astype方法手动将每列数据类型转换为与数据库中的数据类型相匹配。这种方法需要手动指定每列的数据类型,但可以确保写入数据库的数据类型与数据库中的数据类型匹配。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值