How to implement session.add on this code:
Question: So, I need some help to make this script run faster, below is my script.

    
    
    #!/usr/bin/env python
    import glob,os, csv
    from sqlalchemy import *
    
    count = 0
    served_imsi = []
    served_imei = []
    served_msisdn = []
    sgsn_address = []
    ggsn_address = []
    charging_id = []
    apn_network = []
    location_area_code = []
    routing_area = []
    cell_identity = []
    service_area_code = []
    s_charging_characteristics = []
    plmn_id = []
    
    path = '/home/cneps/cdr/*.cdr'
    for file in glob.glob(path):
        f = open(file)
        for lines in f:
            served_imsi.append(lines[17:17+16])
            served_imei.append(lines[47:47+16])
            served_msisdn.append(lines[65:65+18])
            sgsn_address.append(lines[83:83+32])
            ggsn_address.append(lines[115:115+32])
            charging_id.append(lines[147:147+10])
            apn_network.append(lines[157:157+63])
            location_area_code.append(lines[296:296+4])
            routing_area.append(lines[300:300+2])
            cell_identity.append(lines[302:302+4])
            service_area_code.append(lines[306:306+4])
            s_charging_characteristics.append(lines[325:325+2])
            plmn_id.append(lines[327:327+6])
    
    db = create_engine('sqlite:///CDR.db',echo=False)
    metadata = MetaData(db)
    CDRS = Table('CDRS', metadata, autoload=True)
    i = CDRS.insert()
    
    while count < len(served_imei):
    
            i.execute(Served_IMSI=served_imsi[count],
            Served_IMEI=served_imei[count],
            Served_MSISDN=served_msisdn[count],
            SGSN_Address=sgsn_address[count],
            GGSN_Address=ggsn_address[count],
            Charging_ID=charging_id[count],
            APN_Network=apn_network[count],
            LAC=location_area_code[count],
            RAC=routing_area[count],
            Cell_Identity=cell_identity[count],
            Service_Area_Code=service_area_code[count],
            S_Charging_Characteristics=s_charging_characteristics[count],
            PLMN_ID=plmn_id[count])
            count += 1
    
    for files in glob.glob(path):
            os.remove(files)
    

The code is fine. But it takes too long, I asked a friend, and he suggested
using session.add to make things faster.

The solution I came with was something like this:

    
    
    for item in lists:
        session.add(item)
    

but that would work only for one list, and I have 14 lists.

Does anyone have an idea of how to implement session.add to my code, instead
of execute?

Thank you!


Answer: There are two (primary) ways you can use SQLAlchemy. The way that you are
currently doing it is using the [SQLAlchemy Expression
Language](http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html) API. Using
`session.Add` is part of the [Object
Relational](http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html) API. They
serve two separate purposes, and I would recommend reading some of the
documentation to get an idea of the differences.

The Object Relational API (more generally referred to as
[ORM](http://en.wikipedia.org/wiki/Object-relational_mapping), or "Object
Relational Mapper") uses the SQLAlchemy Expression Language underneath, so
anything you can do in the ORM API you can do in the SQLAlchemy Expression API
(although with different syntax). So, I'd recommend that unless you really
need to, don't try to switch your code over to the other API quite yet.

As an alternative to completely rewriting your code, note that you are doing
this:

    
    
    CDRS = Table('CDRS', metadata, autoload=True)
    i = CDRS.insert()
    

`i` becomes an [insert
expression](http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#coretutorial-
insert-expressions). You can use it to dynamically create insert statements.
You can try this by printing out `i`: it will look like a SQL string with
parameters (but note that the value is not actually a string; it is an object
that helps automatically create a SQL string). When you pass that insert
object over to the `execute` method (along with all of the parameters), the
SQL string is generated an executed.

Right now, you are using the execute method on the insert statement. The
preferred way is to use the execute method on the engine. So, instead of...

    
    
    i.execute(...)
    

...you should do...

    
    
    db.execute(i, ...)
    

The performance issue is that you are running into involves using `execute`
once for each row. Thus, each INSERT is running in its own transaction, which
could be slow. You could do something like...

    
    
    all_data = []
    
    while count < len(served_imei):
        data = {
            'Served_IMSI' : served_imsi[count],
            'Served_IMEI' : served_imei[count],
            ...
        }
        all_data.append(data)
    
        count += 1
    

Now, `all_data` contains a list of dictionaries, with each dictionary
containing the keys (column names) and the values (column values) for the
insert statement. Then, you can use...

    
    
    db.execute(i, all_data)
    

I would recommend enabling `echo` while you are working on this to learn what
SQLAlchemy is doing under the hood:

    
    
    db = create_engine('sqlite:///CDR.db',echo=True) # Change `echo` from False to True
    

Your next step would be to remove your two loops, and create the dictionary
that I just showed directly from the section of the code where are reading
from the file. For example:

    
    
    #!/usr/bin/env python
    import glob,os, csv
    from sqlalchemy import *
    
    db = create_engine('sqlite:///CDR.db',echo=False)
    metadata = MetaData(db)
    CDRS = Table('CDRS', metadata, autoload=True)
    insert_statement = CDRS.insert()
    
    all_data = []
    
    path = '/home/cneps/cdr/*.cdr'
    for file in glob.glob(path):
        # Use "with" to automatically close "f" when we're done with it.
        with open(file) as f:
            for lines in f:
                d = {
                    'Served_IMSI' : lines[17:17+16],
                    ....
                }
                all_data.append(d)
    
    # No need for that "while count < len(served_imei)" loop, we can now just do...
    db.execute(insert_statement, all_data)
    

