Jan-30-2020, 08:28 AM
I have a script used to migrate data from SQLite to Postgres. I just use a for loop to transfer tables one by one. Now, I want to experiment with transfering multiple tables in concurrency using thread, multiprocessing or asyncio to speed up the program then compare the runtimes between those ways.
I'm testing with multiprocessing but i got some errors. Can someone help me? How do you do one of those ways?
Here is my script:
I'm testing with multiprocessing but i got some errors. Can someone help me? How do you do one of those ways?
Here is my script:
import psycopg2, sqlite3, sys import time import multiprocessing sqdb="C://Users//duongnb//Desktop//Python//SqliteToPostgreFull//testmydb6.db" sqlike="table" pgdb="testmydb11" pguser="postgres" pgpswd="1234" pghost="127.0.0.1" pgport="5432" consq=sqlite3.connect(sqdb) cursq=consq.cursor() tabnames=[] print() cursq.execute('SELECT name FROM sqlite_master WHERE type="table" AND name LIKE "%table%";') 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 = multiprocessing.Process(target = copyTable, args = (table)) p.start() for table in tabnames: p.join() print("All processes finished.") duration = time.time() - start_time print(f"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\sqq.py"
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-4:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-8:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-1:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-10:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 7 were given
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-2:
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
All processes finished.
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Duration 0.16455531120300293 seconds[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Process Process-6:
Process Process-9:
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
Traceback (most recent call last):
Traceback (most recent call last):
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
Process Process-5:
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
Traceback (most recent call last):
Process Process-3:
TypeError: copyTable() takes 1 positional argument but 6 were given
TypeError: copyTable() takes 1 positional argument but 6 were given
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
Traceback (most recent call last):
[('table1',), ('table2',), ('table3',), ('table4',), ('table5',), ('table6',), ('table7',), ('table8',), ('table9',), ('table10',)]
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
Process Process-7:
TypeError: copyTable() takes 1 positional argument but 6 were given
Traceback (most recent call last):
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 297, in _bootstrap
self.run()
File "C:\Users\duongnb\AppData\Local\Continuum\anaconda3\lib\multiprocessing\process.py", line 99, in run
self._target(*self._args, **self._kwargs)
TypeError: copyTable() takes 1 positional argument but 6 were given
(base) C:\Users\duongnb\Desktop\Python>C:/Users/duongnb/AppData/Local/Continuum/anaconda3/Scripts/activate
(base) C:\Users\duongnb\Desktop\Python>conda activate base
(base) C:\Users\duongnb\Desktop\Python>