Create django models from a CSV source

Question

AdWords for some reason doesn't have a normal REST api, it uses instead a SOAP api which either returns a .csv file or recently a string.

I'm using django and I need to save results in my database. Currently I use Pandas to transfor the string to a dataframe, and then I iterate through it and save it in my database. However this process is slow and inefficient. I don't want to use pandas just for this. Would AST work in my situation?

What's the fastest way to do this?

My code with pandas looks like this:

abc = report_downloader.DownloadReportAsString(report, skip_report_header=True, skip_column_header=False,skip_report_summary=True)
print(abc)
print(type(abc))
df = pd.read_csv(StringIO(abc), sep=",",)
#print (df.to_string())

for index, row in df.iterrows():

    ctr = float(float(row['CTR'].strip('%')) / 100)
    conv_rate = float(float(row['Conv. rate'].strip('%')) / 100)
    cost = row['Cost']/1000000
    cpm = row['Avg. CPM']/1000000
    avg_cpc = row['Avg. CPC']/1000000
    def_data = {
                'impressions':row['Impressions'],
                'ctr':ctr,
                'avg_cpc':avg_cpc,
                'conversion_rate':conv_rate,
                'clicks':row['Clicks'],
                'cost':cost,
                'cpm':cpm,
                'conversions':row['Conversions'],
                }


    obj, created = AdWordsData.objects.get_or_create(
                                                    user=user,
                                                    date=row['Day'],
                                                    campaign=row['Campaign'],
                                                    ad_group=row['Ad group'],
                                                    final_url=row['Final URL'],
                                                    tracking_template=row['Tracking template'],
                                                    defaults=def_data,
                                                    )

    if not created:
        obj.__dict__.update(**def_data)
        obj.save()

else:
    print ('No campaigns were found.')

ABC (the data return by adwords is a string and looks like this:

Day,Campaign,Ad group,Final URL,Tracking template,Impressions,CTR,Avg. CPC,Conv. rate,Clicks,Cost,Avg. CPM,Conversions
2017-09-08,mysite.me Many Keywords,Many Keywords,https://mysite.me, --,364,0.82%,73333,0.00%,3,220000,604396,0.00
2017-09-10,mysite.me Many Keywords,Many Keywords,https://mysite.me, --,1562,3.46%,435926,0.00%,54,23540000,15070423,0.00
2017-09-11,mysite.me Many Keywords,Many Keywords,https://mysite.me, --,1806,3.49%,387619,0.00%,63,24420000,13521595,0.00

score 0 · Answer 1 · answered Nov 15 '17 at 12:45

Using pandas to load a csv is indeed overkill I guess. You could iterate over you csv lines using basic python.

Or better, you could use an existing django library that allow you to create a list of django model from a csv file / input. Ex: django import-export

>>> import tablib
>>> from import_export import resources
>>> from core.models import Book
>>> book_resource = resources.modelresource_factory(model=Book)()
>>> dataset = tablib.Dataset(['', 'New book'], headers=['id', 'name'])
>>> result = book_resource.import_data(dataset, dry_run=True)
>>> print result.has_errors()
False
>>> result = book_resource.import_data(dataset, dry_run=False)

Create django models from a CSV source

1 Answers1