Python Forum

Full Version: Error SQLite objects created in a thread can only be used in that same thread.
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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 :
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.
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.)
@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 ?
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/3935...oncurrency

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