+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Error SQLite objects created in a thread can only be used in that same thread. (/thread-24108.html)
Error SQLite objects created in a thread can only be used in that same thread. - binhduonggttn - Jan-31-2020
My script used to migrate data from SQLite to Postgres in Python. I'm using threading module to speed up transfer tables but I got a error
Error:sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same threadCan someone help me edit it? Thanks
My script :
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 ")My error:Error:Microsoft Windows [Version 10.0.18362.535]
(c) 2019 Microsoft Corporation. All rights reserved.
(base) C:\Users\duongnb\Desktop\Python>python -u "c:\Users\duongnb\Desktop\Python\SqliteToPostgreFull\MigrateThreading.py"
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Exception in thread Thread-1:
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 6924 and this is thread id 12180.
Exception in thread Thread-4:
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 6924 and this is thread id 8240.
Exception in thread Thread-3:
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 6924 and this is thread id 228.
Exception in thread Thread-2:
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 6924 and this is thread id 8852.
Exception in thread Thread-6:
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 6924 and this is thread id 9344.
Exception in thread Thread-7:
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 6924 and this is thread id 6104.
Exception in thread Thread-8:
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 6924 and this is thread id 2516.
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 6924 and this is thread id 1912.
Exception in thread Thread-9:
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 6924 and this is thread id 3152.
Duration {duration} seconds
Exception in thread Thread-5:
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 6924 and this is thread id 10416.RE: Error SQLite objects created in a thread can only be used in that same thread. - ibreeden - Jan-31-2020
I am not very sure as I am not aquainted to SQLite and Postgres, but what strikes me is this: in line 13 and 14 you open a connection and a cursor:
consq=sqlite3.connect(sqdb) cursq=consq.cursor()Then you obtain the tablenames and you define a function. In line 54 you close the connection to the database:
consq.close()And then the program starts, calling the function. But the connection does not exist anymore. As the function is designed to run in a separate thread I believe the function should open it's own connection and cursor. (And close them at the end of the function.)
RE: Error SQLite objects created in a thread can only be used in that same thread. - binhduonggttn - Jan-31-2020
@ibreeden You're right. I've removed it but it's still running fail. I asked Stackoverflow, they said I have two threads, the first is the
"__main__"thread, the second is
.Thread(target = copyTable, args = (table,)). How can I do ?
RE: Error SQLite objects created in a thread can only be used in that same thread. - DeaD_EyE - Jan-31-2020
You're writing from a different thread, as the connection was created in.
You can use the consumer pattern or ignore that it's threaded:
consq = sqlite3.connect(sqdb, check_same_thread=False)StackOverflow. https://stackoverflow.com/questions/393554/python-sqlite3-and-concurrency
Before you use it, check if it's corrupting your data.