Python Forum
Error SQLite objects created in a thread can only be used in that same thread.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error SQLite objects created in a thread can only be used in that same thread.
#1
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.
Reply
#2
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.)
Reply
#3
@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 ?
Reply
#4
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.
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Process doesn't work but Thread work ! mr_byte31 4 498 Oct-18-2021, 06:29 PM
Last Post: mr_byte31
  Reddit bot thread Gabriel777 0 335 Sep-20-2021, 02:57 AM
Last Post: Gabriel777
Thumbs Down Thread not Working michaelserra 4 851 Aug-04-2021, 10:08 AM
Last Post: michaelserra
  Error while transferring data from sqlite to elasticsearch - please help! ps96068 1 867 Jun-12-2021, 09:24 AM
Last Post: ibreeden
  Error on open of file created with tempfile.TemporaryDirectory() Brian177 4 1,851 Apr-05-2021, 07:12 PM
Last Post: Brian177
  Trying to get all this on a single thread stylingpat 0 686 Mar-16-2021, 08:12 PM
Last Post: stylingpat
  threadlocals are garbage collected before thread ends akv1597 0 714 Mar-09-2021, 12:13 PM
Last Post: akv1597
  Thread control dkinsler 0 553 Nov-29-2020, 04:50 PM
Last Post: dkinsler
  [split] New thread/topic rajp1497 1 728 Sep-24-2020, 01:55 AM
Last Post: micseydel
  SQLite Unique constraint failed error djwilson0495 3 4,198 Aug-14-2020, 05:23 PM
Last Post: ndc85430

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020