Error SQLite objects created in a thread can only be used in that same thread.

[binhduonggttn]

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 thread

.
Can someone help me edit it? Thanks
My script :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

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.

[ibreeden]

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:

1 2	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:

1	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.)

[binhduonggttn]

@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

1	"__main__"

thread, the second is

1	.Thread(target = copyTable, args = (table,))

. How can I do ?

[DeaD_EyE]

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:

1	consq = sqlite3.connect(sqdb, check_same_thread=False)

StackOverflow. https://stackoverflow.com/questions/3935...oncurrency

Before you use it, check if it's corrupting your data.