to_sql()插入數(shù)據(jù)到mysql中所遇到的問題

使用pymysql驅(qū)動(dòng)API，出現(xiàn)如下錯(cuò)誤：

DatabaseError: Execution failed on sql ‘SELECT name FROM sqlite_master WHERE type=‘table’ AND name=?;’: not all arguments converted during string formatting

1. pandas的數(shù)據(jù)表插入數(shù)據(jù)到mysql中所遇到的問題

1.1. pymysql驅(qū)動(dòng)接口問題

根據(jù)以往經(jīng)驗(yàn)，mysql操作很簡單，安裝pymysql驅(qū)動(dòng)即可。

pip install pymysql

編寫了通過pandas的to_sql寫入數(shù)據(jù)庫的代碼。

import pandas as pd
import datetime
import uuid  # 數(shù)據(jù)庫主鍵唯一
import pymysql
def mysql_db():
    # 連接數(shù)據(jù)庫肯定需要一些參數(shù)
    conn = pymysql.connect(
        host="192.168.**.**",
        port=3306,
        database="M*****DB",
        charset="utf8",
        user="ty",
        passwd="****"
    )
    return conn
# df = 略去
# 增加唯一主鍵uuid
id = []
for i in range(len(df)):
    id.append(uuid.uuid1())
# 增加記錄寫入時(shí)間
df['CreateTime'] = datetime.datetime.now()
df['id'] = id
# 下面是出現(xiàn)問題的地方
conn = mysql_db()
tablename = '******'
dd = df[colsname]
# 浮點(diǎn)型截?cái)鄶?shù)據(jù)處理
for k,v in cols_len.items():
    dd[k] = dd[k].round(v)
# 下面是出現(xiàn)問題的地方
dd.to_sql(tablename, conn,index=False, if_exists='append')

執(zhí)行程序，報(bào)出如下問題：

DatabaseError: Execution failed on sql ‘SELECT name FROM sqlite_master WHERE type=‘table’ AND name=?;’: not all arguments converted during string formatting

嘗試執(zhí)行查詢語句：

df0=pd.read_sql('select * from S*******y',conn)
df0

一切很正常，結(jié)果如下：

1.2. 關(guān)于數(shù)據(jù)庫主鍵

主鍵一般使用uuid，python中的uuid模塊基于信息如MAC地址、時(shí)間戳、命名空間、隨機(jī)數(shù)、偽隨機(jī)數(shù)來uuid。

具體方法有如下幾個(gè)：

• uuid.uuid1()：基于MAC地址，時(shí)間戳，隨機(jī)數(shù)來生成唯一的uuid，可以保證全球范圍內(nèi)的唯一性。
• uuid.uuid2()：算法與uuid1相同，不同的是把時(shí)間戳的前4位置換為POSIX的UID。不過需要注意的是python中沒有基于DCE的算法，所以python的uuid模塊中沒有uuid2這個(gè)方法。
• uuid.uuid3(namespace,name)：通過計(jì)算一個(gè)命名空間和名字的md5散列值來給出一個(gè)uuid，所以可以保證命名空間中的不同名字具有不同的uuid，但是相同的名字就是相同的uuid了。其中，namespace并不是一個(gè)自己手動(dòng)指定的字符串或其他量，而是在uuid模塊中本身給出的一些值。比如uuid.NAMESPACE_DNS，uuid.NAMESPACE_OID，uuid.NAMESPACE_OID這些值。這些值本身也是UUID對(duì)象，根據(jù)一定的規(guī)則計(jì)算得出。
• uuid.uuid4()：通過偽隨機(jī)數(shù)得到uuid，是有一定概率重復(fù)的
• uuid.uuid5(namespace,name)：和uuid3基本相同，只不過采用的散列算法是sha1。

使用時(shí)，按數(shù)據(jù)集中的記錄數(shù)，一次性生成uuid列表，并入到表中。

2. pandas的to_sql()有個(gè)隱藏的坑

解決辦法

查了pandas官網(wǎng)和其他不少有關(guān)to_sql的文章，都沒有看到有提及數(shù)據(jù)庫連接這個(gè)坑，不過在pandas官網(wǎng)的to_sql例子和其他文章的to_sql中都有一個(gè)共同點(diǎn)，就是引用sqlalchemy這個(gè)第三方ORM庫來進(jìn)行連接的，據(jù)此解決to_sql這個(gè)報(bào)錯(cuò)。

首先，安裝 sqlalchemy。

pip install sqlalchemy

修改代碼，增加函數(shù)：

def mysql_engine_db():
    # 連接數(shù)據(jù)庫肯定需要一些參數(shù)
    engine = create_engine(
        'mysql+pymysql://ty:***@192.168.**.**:3306/M*******DB'
        # mysql+pymysql://用戶: 密碼@url: 端口/數(shù)據(jù)庫
    )
    return engine
# 下面是出現(xiàn)問題的地方
engine= mysql_engine_db()
tablename = '******'
dd = df[colsname]
# 浮點(diǎn)型截?cái)鄶?shù)據(jù)處理
for k,v in cols_len.items():
    dd[k] = dd[k].round(v)
# 下面是出現(xiàn)問題的地方
dd.to_sql(tablename, engine,index=False, if_exists='append')   

如此簡單的解決問題！

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論