Python: Import XML to Pandas dataframe, and then dataframe to Sqlite database

import_xml_to_dataframe_to_sql.py

import xml.etree.ElementTree as ET
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

# do this if running in jupyter
# pd.set_option('display.max_columns', None)

# convert XML to dataframe (assumes only one layer of nesting)
def xml2df(xml_data):
    root = ET.XML(xml_data) # element tree
    all_records = []
    for i, child in enumerate(root):
        record = {}
        for subchild in child:
            record[subchild.tag] = subchild.text
        all_records.append(record)
    df = pd.DataFrame(all_records)

    # how to make datetimes from unix epoch ints
    df['CreatedTimestamp'] = pd.to_datetime(df['CreatedDate'], unit='s')
    df['ModifiedTimestamp'] = pd.to_datetime(df['ModifiedDate'], unit='s')

    return df

# load XML to dataframe (gotta be small)
xml_data = open('example.xml').read()
df = xml2df(xml_data)

# export dataframe to sqlite
engine = create_engine('sqlite:///example.db')
name = 'my_table'
df.to_sql(name, engine, if_exists='replace')

# see how many records loaded
Session = scoped_session(sessionmaker(bind=engine))
s = Session()
result = s.execute('SELECT COUNT(*) FROM my_table').fetchall()
print("{} records loaded".format(result[0][0]))

Just a short question. Would it be possible for you to just quickly explain to me how to add a second layer of nesting? Your script works beautifully for my task.

@LukasKania - for one additional layer of nesting, you'll want to just add another nested loop under for subchild in child (ie: for subsubchild in subchild). If you have nesting further than that, you will probably want to look into re-factoring that out into a recursive function or using direct selectors into the elements you care about, but if you can keep it simple that's always preferred.

thanks for your quick reply. if i add one more for loop i literally get to the level i want to reach but not into the data. im not that experienced with more complex .xml. i need to extract different 66 attributes which is going to be hard by direct selecting hey? is there no way to clean the namespace, prefix and tag (able to that), turn the output into a list and select that for further investigation? thanks anyways!