python - 错误在一个线程中创建的 SQLite 对象只能在同一个线程中使用
问题描述
我的脚本用于将数据从 SQLite 迁移到 Python 中的 Postgres。我正在使用threading
模块来加快传输表的速度,但出现错误sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.
有人可以帮我编辑吗?谢谢。我的脚本:
import psycopg2, sqlite3, sys
import time
import threading
sqdb="C://Users//duongnb//Desktop//Python//SqliteToPostgreFull//testmydb6.db"
sqlike="table"
pgdb="testmydb13"
pguser="postgres"
pgpswd="1234"
pghost="127.0.0.1"
pgport="5432"
consq=sqlite3.connect(sqdb)
cursq=consq.cursor()
tabnames=[]
cursq.execute('''SELECT name FROM sqlite_master WHERE type="table" AND name LIKE "'''+sqlike+'''%";''')
tabgrab = cursq.fetchall()
for item in tabgrab:
tabnames.append(item[0])
print(tabgrab)
def copyTable(table):
cursq.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name = ?;", (table,))
create = cursq.fetchone()[0]
cursq.execute("SELECT * FROM %s;" %table)
rows=cursq.fetchall()
colcount=len(rows[0])
pholder='%s,'*colcount
newholder=pholder[:-1]
try:
conpg = psycopg2.connect(database=pgdb, user=pguser, password=pgpswd,
host=pghost, port=pgport)
curpg = conpg.cursor()
curpg.execute("DROP TABLE IF EXISTS %s;" %table)
create = create.replace("AUTOINCREMENT", "")
curpg.execute(create)
curpg.executemany("INSERT INTO %s VALUES (%s);" % (table, newholder),rows)
conpg.commit()
if conpg:
conpg.close()
except psycopg2.DatabaseError as e:
print ('Error %s' % e)
sys.exit(1)
finally:
print("Complete")
consq.close()
if __name__ == "__main__":
start_time = time.time()
for table in tabnames:
p = threading.Thread(target = copyTable, args = (table,))
p.start()
for table in tabnames:
p.join()
duration = time.time() - start_time
print("Duration {duration} seconds ")
我的错误:
Exception in thread Thread-10:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\threading.py", line 926, in _bootstrap_inner
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\threading.py", line 870, in run
self._target(*self._args, **self._kwargs)
File "c:\Users\duongnb\Desktop\Python\SqliteToPostgreFull\MigrateThreading.py", line 25, in copyTable
cursq.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name = ?;", (table,))
sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 9744 and this is thread id 12712.
解决方案
推荐阅读
- python - 使用 win32evtlog 模块读取过去一小时的 Windows 事件日志
- spring - REST springboot 服务(XML)中的 406 HTTP 响应
- sql - SQL 查询中日期的 Case When 语句
- azure-cosmosdb - 不同的查询在 Cosmos DB 中无法正常工作
- javascript - 在 React-Bootstrap 中有条件地禁用选项卡
- python - Selenium(python)显式等待超时错误
- spring-boot - 我们真的需要为 Spring Boot 管理客户端添加 Spring Boot Starter Web 吗?
- asp.net - Azure B2C 应用程序角色(图形 api)
- c - 如何从 Windows 命令行调试 eclipse c 项目?
- java - SpringBoot:如何使用响应式编程调用 API 链?