import pandas as pd
import sqlite3
from pandas import DataFrame
from sqlalchemy import create_engine
con = sqlite3.connect(r"C:\Users\K\Desktop\excel-upload-sqlite3\mins\db.sqlite3" )
sql = "SELECT * FROM rilegoule"
df = pd.read_sql(sql, con)
print(df)
name_dataframe = df[df["name" ]=="kangkang" ]
id = name_dataframe.loc[name_dataframe.last_valid_index(), "id" ]
print(id)
import pandas as pd
import sqlite3
from pandas import DataFrame
from sqlalchemy import create_engine
excel_data = pd.read_excel(r"C:\Users\K\Desktop\rilegoule.xlsx" )
excel_name_list = excel_data['name' ].tolist()
id=1
for name in excel_name_list:
id = id+1
excel_data.loc[:,"id" ] = id
print(excel_data)
for i in range(1 ,100 ):
fund_id = 'FI' +'0' *(5 -len(str(i )))+str(i )
print(fund_id)
import pandas as pd
filefullpath = r"C:\Users\K\Desktop\excel-upload-sqlite3\mins\upload\upload\华泰大赛参赛私募基金数据填报模板.xlsx"
sheetname = 1
excel_df = pd.read_excel(filefullpath, sheetname)
excel_df = excel_df.dropna(how="all" )
excel_df = excel_df.dropna(axis=1 , how="all" )
excel_df = excel_df.T
excel_df.columns = excel_df.loc["公司资料简介" ]
excel_df = excel_df.drop("公司资料简介" , axis=0 , inplace=False )
excel_df.index=range(len(excel_df))
print(excel_df)
import pandas as pd
import sqlite3
from pandas import DataFrame
from sqlalchemy import create_engine
con = sqlite3.connect(r"C:\Users\K\Desktop\excel-upload-sqlite3\mins\db.sqlite3" )
sql = "SELECT rilegoule.name FROM rilegoule"
sql_df = pd.read_sql(sql, con)
name_list = sql_df['name' ].tolist()
sql_number = len(name_list)
i = 0
for name in sql_df['name' ].unique():
i = i+1
with con:
cur = con.cursor()
cur.execute("""UPDATE rilegoule SET id=? WHERE name=?""" , (i, name))
print("tosql!" )
excel_df = pd.read_excel(r"C:\Users\K\Desktop\excel-upload-sqlite3\rilegoule.xlsx" )
excel_name_list = excel_df['name' ].tolist()
for name in excel_name_list:
if name in name_list:
con = sqlite3.connect(r"C:\Users\K\Desktop\excel-upload-sqlite3\mins\db.sqlite3" )
sql = "SELECT * FROM rilegoule"
print(name)
sql_df = pd.read_sql(sql, con)
name_dataframe = sql_df[sql_df["name" ]==name]
id = name_dataframe.loc[name_dataframe.last_valid_index(), 'id' ]
index = excel_df[excel_df["name" ]==name]
commit_data = pd.DataFrame(data=excel_df, index=[index.last_valid_index()], columns=excel_df.columns)
commit_data.loc[index.last_valid_index(), "id" ] = id
name = commit_data.loc[index.last_valid_index(), "name" ]
class1 = str(commit_data.loc[index.last_valid_index(), "class" ])
with con:
cur = con.cursor()
cur.execute("""UPDATE rilegoule SET name=?,class=? WHERE id=?""" , (name, class1, id))
print("if" )
else :
sql_number = sql_number+1
index = excel_df[excel_df["name" ]==name]
commit_data = pd.DataFrame(data=excel_df, index=[index.last_valid_index()], columns=excel_df.columns)
commit_data.loc[index.last_valid_index(), "id" ] = sql_number
commit_data.to_sql("rilegoule" , con, if_exists="append" , index=False )
print("else" )
print(excel_name_list)
print(name_list)
'''
更新column的顺序
df = df[['id', 'class', 'address']]
old_names = ['$a', '$b', '$c', '$d', '$e']
new_names = ['a', 'b', 'c', 'd', 'e']
df.rename(columns=dict(zip(old_names, new_names)), inplace=True)
frame = frame[['column I want first', 'column I want second'...etc.]]
'''
import pandas as pd
from sqlalchemy import create_engine
import sqlite3
import xlrd
con = sqlite3.connect(r"C:\Users\K\Desktop\excel-upload-sqlite3\mins\db.sqlite3" )
sql = "SELECT org_info.org_full_name FROM org_info"
data = pd.read_sql(sql, con)
fund_name_list = data['org_full_name' ].tolist()
print(fund_name_list)
org_id = 0
for org_full_name in data['org_full_name' ].unique():
org_id = org_id+1
with con:
cur = con.cursor()
cur.execute("""UPDATE org_info SET org_id=? WHERE org_full_name=?""" , (org_id, org_full_name))
print("tosql!" )