代码块1

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
#依次对数据库中的每一行添加一列id
for name in sql_df['name'].unique():
    i = i+1
    #sql_df.loc[sql_df['name'] == name, "id"] = i
    with con:
        cur = con.cursor()
        cur.execute("""UPDATE rilegoule SET id=? WHERE name=?""", (i, name))
        # sql_df.to_sql("rilegoule", con, if_exists="append", index=False)#失败，新增的一行在以前的后面，id没有增加在之前行的后面
        # sql_df.to_sql("rilegoule", con, if_exists="replace", index=False)#失败，全部替换，只剩下name和id两行
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)
        #构建单列dataframe
        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))
        #commit_data.to_sql("rilegoule", con, if_exists="replace", index=False)#
        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 = excel_data.loc[index.last_valid_index()]
        # commit_data.to_sql("rilegoule", con, if_exists="append", index=False)
        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!")