python sqlite3 - 游标与连接 - 执行 UPDATE 语句

Question

我在执行 UPDATE 语句时遇到问题。 在下面的代码中，当我写入con.execute()时，它会写入数据库，但如果我写入c.execute()它不会写入数据库。 长注释是有问题的代码所在。

最相关的代码段

def write_data(con, c):
geolocator = Nominatim()
table = c.execute('SELECT * FROM playerdata WHERE birth_place_long is null AND birth_place is not null').fetchall()
for i in table:
try:
location = getgeo(i[4], geolocator)
lat, long = location.latitude, location.longitude
except:
print(i[0])
'''  changing to c.execute doesnt work '''
con.execute('UPDATE playerdata SET birth_place_lat = ?, birth_place_long = ? WHERE id = ?', (1, 1, i[0]))
print("failed" + i[1] + " " + i[2])
con.commit()
else:
c.execute('UPDATE playerdata SET birth_place_lat = ?, birth_place_long = ? WHERE id = ?', (lat, long, i[0]))
print(i[1] + " " + i[2])
con.commit()
con.close()

完整代码如下：

import csv
from geopy.geocoders import Nominatim
import sqlite3
import socket
import geopy.exc
import pandas as pd
def connect_db():
con = sqlite3.connect('c:/s_db2.sqlite3')
c = con.cursor()
return con, c

def getgeo(place, geolocator):
try:
location = geolocator.geocode(place)
return location
except (socket.timeout):
return getgeo(place, geolocator)
except geopy.exc.GeocoderTimedOut:
return 0

def write_data(con, c):
geolocator = Nominatim()
table = c.execute('SELECT * FROM playerdata WHERE birth_place_long is null AND birth_place is not null').fetchall()
for i in table:
try:
location = getgeo(i[4], geolocator)
lat, long = location.latitude, location.longitude
except:
print(i[0])
'''  changing to c.execute doesnt work '''
con.execute('UPDATE playerdata SET birth_place_lat = ?, birth_place_long = ? WHERE id = ?', (1, 1, i[0]))
print("failed" + i[1] + " " + i[2])
con.commit()
else:
c.execute('UPDATE playerdata SET birth_place_lat = ?, birth_place_long = ? WHERE id = ?', (lat, long, i[0]))
print(i[1] + " " + i[2])
con.commit()
con.close()
while True:
write_data(connect_db()[0], connect_db()[1])
print("************* re-running ****************")

Answer 1

循环中的这一行：

write_data(connect_db()[0], connect_db()[1])

connect_db()在同一个write_data()调用中被调用两次。

这类似于：

con_1, c_1 = connect_db()
con_2, c_2 = connect_db()
write_data(con_1, c_2)

您正在将连接的实例与其他实例的游标混合在一起。

相反，您可能需要：

con_1, c_1 = connect_db()
write_data(con_1, c_1)

Answer 2

conn 正在使用具有自己设置的连接实例，即自动提交。但是，一个curso，你需要定义自动提交集，或者更确切地说，在执行sql后保留提交。

即在with con: c.execute()后使用con.commit()

注意，如果此逻辑在 try-catch 块(即异常)之后执行，则可能需要在执行此逻辑之前回滚。