Python, SQLAlchemy, how to insert queries in an efficient way with only one commit instruction

Question

I am working with SQLAlchemy and my insert function works correctly. However, i want and i need it to be efficient, therefore since i am inserting inside a "for loop", i would like to commit just once at the end of my program execution.

I am not sure, this kind of thinking applies to SQLAlchemy, so please advice me on the right, efficient way of doing it.

my code will call a insert_query function from a for loop. I do not return the query object that is created inside the function call.

def insert_query(publicId, secret, keyhandle, secretobj):

    #creates the query object 
    sql = secretobj.insert().values(public_id=publicId, keyhandle=keyhandle, secret=secret)
    #insert the query
    result = connection.execute(sql)

    return result

#####################
# CALL INSERT BELOW #
#####################


#walk across the file system to do some stuff
for root, subFolders, files in os.walk(path):
    if files:

        do_some_stuff_that_produce_output_for_insert_query()

        #########################
        # here i call my insert #
        #########################
        if not insert_query(publicId, secret, keyhandle, secretobj):
            print "WARNING: could not insert %s" % publicId


#close sqlalchemy
connection.close()

Ian Wilson · Accepted Answer · 2013-07-16 19:10:41Z

3

I think you'd be better off using executemany.

def make_secret(files):
    # You'd have to define how you generate the dictionary to insert.
    # These names should match your table column names.
    return {
        'public_id': None,
        'secret': None,
        'keyhandle': None,
    }
# You can make the whole list of rows to insert at once.
secrets = [make_secret(files) for root, subFolders, files in os.walk(path) if files]
# Then insert them all like this
connection.execute(secretobj.insert(), secrets)

executemany is explained in the second part of this section:

http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#executing-multiple-statements

answered Jul 16, 2013 at 19:10

Ian Wilson

9,3392 gold badges27 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Python, SQLAlchemy, how to insert queries in an efficient way with only one commit instruction

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related