Python-写入MySQL

Question

我想将一些数据从 SQLite 数据库复制到 MySQL 数据库...但是，如果我有 3 个 sqlite 数据集，则脚本会在我的目标数据库(mysql)中创建 3 个新表，但仅在第一个和第二个数据库中插入数据...第三个保持为空..但每个查询我仍然得到一个“1”...

import sqlite3
import pymysql as mysql
db_connection_source = sqlite3.connect(db_path)
db_connection_target = mysql.connect("localhost", "username", "pw", "strf")
sql_target = db_connection_target.cursor()

data_tables =  []
speed = []
hr = []
elev = []
gps = []

get_tables = db_connection_source.execute("SELECT name FROM sqlite_master WHERE type='table'")
for row_tables in get_tables:
    if row_tables[0].find("chest_second") == False:                    

        table_label = "sport_activity_"+row_tables[0][13:len(row_tables[0])]
        get_data = sql_target.execute("SHOW TABLES LIKE '"+table_label+"'")

        if get_data == 1:
            print("[X] Old Data found: "+row_tables[0])
        else:
            print("[ ] New Data found: "+row_tables[0])
            data_tables.append(row_tables[0])

for chest_sec in data_tables:
    get_data = db_connection_source.execute("SELECT id, speed, hr, elevation, lat, lon from "+chest_sec)

    table_label = "sport_activity_"+chest_sec[13:len(chest_sec)]
    create_newTable = sql_target.execute("CREATE TABLE `"+table_label+"`(`id` INT NOT Null AUTO_INCREMENT,"
                                         "`speed` FLOAT(16,10) NULL,"
                                         "`bpm` FLOAT(16,10) NULL,"
                                         "`elev` FLOAT(16,10) NULL,"
                                         "`gps_lat` FLOAT(16,10) NULL,"
                                         "`gps_lon` FLOAT(16,10) NULL,"
                                         "`raw_filename` TEXT NULL,"
                                         "PRIMARY KEY (`id`))")

    print ("["+table_label+"] Copying data in database")

    check_speed = "no"
    check_bpm = "no"
    check_elev = "no"
    check_lat = "no"
    check_lon = "no"

    for row in get_data:


        if float(row[1]) > 0:
            check_speed = "yes"

        if row[2] > 0: 
            check_bpm = "yes"

        if row[3] > 0: 
            check_elev = "yes"

        if row[4] > 0:
            check_lat = "yes"

        if row[5] > 0: 
            check_lon = "yes"

        query = ("INSERT INTO "+table_label+" (speed, bpm, elev, gps_lat, gps_lon, raw_filename)"
                "VALUES ('"+str(row[1])+"','"+str(row[2])+"','"+str(row[3])+"','"+str(row[4])+"','"+str(row[5])+"','"+str(chest_sec)+"')")             
        print(query)
        sql_target.execute(query)


    print ("["+table_label+"] Indexing new entry")


    date_raw = chest_sec[13:len(chest_sec)]
    date_new = date_raw[0:4]+"-"+date_raw[5:7]+"-"+date_raw[8:10]+" "+date_raw[11:13]+":"+date_raw[14:16]+":"+date_raw[17:19]



    write_123 = sql_target.execute("INSERT INTO sport_index (datum, speed_data, hr_data, elev_data, strength_data, review, gps_data, second_id)"
                       "VALUES ('"+str(date_new)+"','"+str(check_speed)+"','"+str(check_bpm)+"','"+str(check_elev)+"','0','1','"+str(check_lat)+"','"+str(chest_sec)+"')")
    print (write_123)

Answer 1

尝试在最后使用 db_connection_target.commit() 或在创建连接时使用 autocommit=True