pandas DataFrame.to_sql()用法小結(jié)

更新時(shí)間：2024年02月21日 10:18:12 作者：stone0823

Pandas是基于NumPy的一種工具,該工具是為了解決數(shù)據(jù)分析任務(wù)而創(chuàng)建的,本文主要介紹了pandas DataFrame.to_sql()用法小結(jié),感興趣的可以了解一下

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 表結(jié)構(gòu)：

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)

我們發(fā)現(xiàn)，to_sql() 并沒(méi)有考慮將 emp_master 表字段的數(shù)據(jù)類型同步到目標(biāo)表，而是簡(jiǎn)單的區(qū)分?jǐn)?shù)字型和字符型，這是第一個(gè)問(wèn)題，第二個(gè)問(wèn)題呢，目標(biāo)表沒(méi)有 primary key。因?yàn)?pandas 定位是數(shù)據(jù)分析工具，數(shù)據(jù)源可以來(lái)自 CSV 這種文本型文件，本身是沒(méi)有嚴(yán)格數(shù)據(jù)類型的。而且，pandas 數(shù)據(jù) to_excel() 或者to_sql() 只是方便數(shù)據(jù)存放到不同的目的地，本身也不是一個(gè)數(shù)據(jù)庫(kù)升遷工具。

但如果我們需要嚴(yán)格保留原表字段的數(shù)據(jù)類型，以及同步 primary key，該怎么做呢？

使用 SQL 語(yǔ)句來(lái)創(chuàng)建表結(jié)構(gòu)

如果數(shù)據(jù)源本身是來(lái)自數(shù)據(jù)庫(kù)，通過(guò)腳本操作是比較方便的。如果數(shù)據(jù)源是來(lái)自 CSV 之類的文本文件，可以手寫 SQL 語(yǔ)句或者利用 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()并不是一個(gè)公開(kāi)的方法，沒(méi)有文檔可以查看。生成的 SQL 語(yǔ)句如下：

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 方式插入數(shù)據(jù)

to_sql() 方法的 if_exists 參數(shù)用于當(dāng)目標(biāo)表已經(jīng)存在時(shí)的處理方式，默認(rèn)是 fail，即目標(biāo)表存在就失敗，另外兩個(gè)選項(xiàng)是 replace 表示替代原表，即刪除再創(chuàng)建，append 選項(xiàng)僅添加數(shù)據(jù)。使用 append 可以達(dá)到目的。

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() 方法中，通過(guò) dtype 參數(shù)指定字段的類型，然后在 mysql 中通過(guò) 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`);')

當(dāng)然，如果數(shù)據(jù)源本身就是 mysql，當(dāng)然不用大費(fèi)周章來(lái)創(chuàng)建數(shù)據(jù)表的結(jié)構(gòu)，直接使用 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')

到此這篇關(guān)于pandas DataFrame.to_sql()用法小結(jié)的文章就介紹到這了,更多相關(guān)pandas DataFrame.to_sql() 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: