to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
import sqlite3 import pandas df = pandas.DataFrame([{'a': 123, 'b': 234, 'c': 456}, {'a': 567, 'b': 678, 'c': 789}]) #存入 with sqlite3.connect('filename.db') as conn: df.to_sql('tablename', conn, index=False) conn.close() #取出 with sqlite3.connect('filename.db') as conn: df2 = pandas.read_sql_query('SELECT * FROM tablename', conn) conn.close()
1 import pandas as pd 2 import sqlite3 3 4 xls_file = 'D:/Excel/01PX.xls' 5 conn = sqlite3.connect('D:/database/ntf.db') 6 df_men = pd.read_excel(xls_file, sheet_name=0) 7 df_men.drop(['核算类别','执行科'], axis=1, inplace=True) 8 df_men.rename(columns={'项目名称':'item', '规格':'spec', '价格':'up', '数量':'num'}, inplace=True) 9 df_men.to_excel('new_men.xls', '门市', index=False) 10 df_men.to_sql('menhe', conn, if_exists='append', index=False) 11 12 df_zhu = pd.read_excel(xls_file, sheet_name=1) 13 df_zhu.drop(['核算类别','执行科'], axis=1, inplace=True) 14 df_zhu.rename(columns={'项目名称':'item', '规格':'spec', '价格':'up', '数量':'num'}, inplace=True) 15 df_zhu.to_excel('new_zhu.xls', '主事', index=False) 16 df_zhu.to_sql('zhuhe', conn, if_exists='append', index=False) 17 conn.close()
df.to_sql('tablename', con=conn)
