slow input on database SQlite3 python3

Question

I am trying to save username on local sqlite3 db using python. I am really new. My Table has 10000 rows and just a column of username but it is taking more than half an hour to inset all 10k values. What is I am doing wrong ? Is there any clue ? My code

 def create_table(channel_username):
    c.execute("CREATE TABLE IF NOT EXISTS {}(user_name UNIQUE)".format(channel_username))
    conn.commit()


def insert_username(channel_username,user_name):
    c.execute("INSERT OR IGNORE INTO {} VALUES(?)".format(channel_username),[user_name])
    conn.commit()


create_table(channel_username)

x= client.get_participants(channel_username, aggressive=True)

z=[]
for user in x:

    z.append(user.username)

fl = [i for i in z if i is not None]

fl.sort()

for user_name in fl:

    insert_username(channel_username,user_name)

print("DBfached successful")

Reading this will give you some pointers. The python sqlite bindings don't have the features or power of the C API, but there's still stuff you can take away from it. Start with transactions, and also read up on the executemany() method. — Shawn
– Shawn, Commented Sep 18, 2018 at 2:45

CL. · Accepted Answer · 2018-09-18 18:55:30Z

1

The inserts are not slow; all the commit() calls are slow.

Remove them, and do the commit() only when you're actually finished.

answered Sep 18, 2018 at 18:55

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

slow input on database SQlite3 python3

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related