折腾了几个小时才发现。python3执行sqlite需要commit,否则修改不生效。
下文csv导入sqlite并把英文日期转为数字
conn= sqlite3.connect("C:\\xx\\s0-data.db")
conn.execute('CREATE TABLE "sessions" ( "Course Code" TEXT, "sdate" DATETIME)')
df = pandas.read_csv("C:\\xxFile.csv")
df.to_sql('sessions', conn, if_exists='append', index=False)
conn.execute('UPDATE sessions set sdate=substr("colname",8)||"-"||substr("colname",4,3)||"-"||substr("colname",1,2)')
conn.execute('UPDATE sessions set sdate=REPLACE(sdate,"JAN","01")')
conn.commit()
conn.close()