Multithreading Python For DB Insert

Question

Hi guys currently i am trying to migrate current db to another and that process unfortunately involves python. I was able to do it single threaded, but it's incredibly slow took hours to finish 1M data. Is there similar method in python like Java executor and futures?

note that user_list is a chunk of 1000/1M

for data in user_list:
    q = """ insert into users(id,name,address,password) 
        Values({id},{name},{address},{password})
        """.format(id=data['id'],name=data['name'],address=data['address'],password=data['password'])
    db.command(q)

I think it would be a whole lot faster if i run for example 8 concurrent threads inserting 8 at a time instead of single thread doing single insert

Which DB are you using? It could be faster, but you would need a separate db connection for each thread. I would first see if your DB supports "bulk inserts" or "batched inserts" - those are usually a lot faster than inserting a single row at a time. — ErikR
– ErikR, Commented Jul 29, 2016 at 20:33
Ah, so I can't use 1 connection for doing n insert simultaneously? I am importing sql to orientdb at the moment. @ErikR — kenlz
– kenlz, Commented Jul 30, 2016 at 2:56
Nope - you'll need one connection per thread. Also see my answer. — ErikR
– ErikR, Commented Jul 30, 2016 at 3:32

ErikR · Accepted Answer · 2016-07-30 03:31:52Z

1

Since you say in the comments that you are using orientdb, have a look at the SQL Batch capability.

Using SQL BATCH does not insert rows in parallel, but it will avoid the round-trip for each command.

You can also use SQL BATCH from Python using the pyorient library:

https://github.com/mogui/pyorient#execute-orientdb-sql-batch

To insert data in parallel you will need to create multiple connections, one for each thread.

answered Jul 30, 2016 at 3:31

ErikR

52.2k9 gold badges78 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kenlz Over a year ago

Yup ended up using SQL Batch, but instead of parsing each columns, I ended up exporting separate CSVs as classes. That'd do for now. Thanks :)

Collectives™ on Stack Overflow

Multithreading Python For DB Insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related