把uuid加入DataFrame中并存入SQLite3数据库

来源：互联网发布：淘宝千人千面是什么编辑：程序博客网时间：2024/05/17 23:28

import pandas as pdimport uuidimport sqlite3df = pd.DataFrame({'Name': ['John Doe', 'Jane Smith', 'John Doe', 'Jane Smith','Jack Dawson','John Doe']})for name in df['Name'].unique():    df.loc[df['Name'] == name, 'UUID'] = str(uuid.uuid4())con = sqlite3.connect(r"C:\Users\Administrator\Desktop\excel-upload-sqlite3\mins\db.sqlite3")df.to_sql("shiliyi", con, if_exists="append", index=False)

如果直接存uuid的话会出现错误，SQLite3不能存入uuid格式，需要编程str类型

Traceback (most recent call last):  File "<pyshell#15>", line 1, in <module>    df.to_sql("shiliyi", con, if_exists="append", index=False)  File "D:\Python\lib\site-packages\pandas\core\generic.py", line 1362, in to_sql    chunksize=chunksize, dtype=dtype)  File "D:\Python\lib\site-packages\pandas\io\sql.py", line 471, in to_sql    chunksize=chunksize, dtype=dtype)  File "D:\Python\lib\site-packages\pandas\io\sql.py", line 1506, in to_sql    table.insert(chunksize)  File "D:\Python\lib\site-packages\pandas\io\sql.py", line 666, in insert    self._execute_insert(conn, keys, chunk_iter)  File "D:\Python\lib\site-packages\pandas\io\sql.py", line 1294, in _execute_insert    conn.executemany(self.insert_statement(), data_list)sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

ADD UUID to DataFrame

using-uuids-in-sqlite

SQLite根本不支持数据类型

import pandas as pdfrom pandas import DataFrameimport sqlite3import uuidfilefullpath = r"C:\Users\Administrator\Downloads\华泰大赛参赛私募基金数据填报模板2.xlsx"df = pd.read_excel(filefullpath, sheetname=0)df = df.dropna(how="all")df = df.dropna(axis=1, how="all")print(df)df = df.Tprint(df)print("###")print(df.loc['公司资料简介'])print("###")df.columns = df.loc['公司资料简介']print(df)df = df.drop('公司资料简介', axis=0, inplace=False)print(df)print(df['★机构简称'])#df = df.add('datetime.datetime',axis='columns')for name in df['★机构简称'].unique():    df.loc[df['★机构简称'] == name, 'UUID'] = str(uuid.uuid4())con = sqlite3.connect(r"C:\Users\Administrator\Desktop\excel-upload-sqlite3\mins\db.sqlite3")df.to_sql("shiliyi", con, if_exists="append", index=False)print("to_sql")

阅读全文

0 0