Django and Pandas - Create an object from a Foreying Key field-CodePudding

I am trying to create objects importing data from an excel with pandas, than creating objects with django.

But i am geting this error message:

Name: ticker, dtype: object
Asset matching query does not exist.
Asset matching query does not exist.
Asset matching query does not exist.
...

My Models

class Asset(models.Model):
    ticker = models.CharField(max_length=255, unique=True)


class Transaction(models.Model):
    id = models.AutoField(primary_key=True)
    OrderChoices = (
        ('Buy', 'Buy'),
        ('Sell', 'Sell'),
    )
    date =  models.DateField(("Date"), default=date.today)
    order = models.CharField(max_length = 8, choices = OrderChoices) 
    asset = models.ForeignKey(Asset, on_delete=models.CASCADE, default=1)
    shares_amount = models.FloatField()
    share_cost = models.FloatField()

And here is my updater file code:

class Command(BaseCommand):

    def handle(self, *args, **options):
        excel_file = "orders.xlsx"
        df_2 = pd.read_excel(excel_file)
        df_2 = df_2[['Date(UTC)','Pair', 'Type', 'Order Amount', 'AvgTrading Price']]
        df_2.columns = ['date', 'ticker', 'order', 'shares_amount','share_cost']
        df_2['date'] = df_2['date'].str[:9]
        df_2['ticker'] = df_2['ticker'].str.replace('USDT', '')
        df_2['order'] = df_2['order'].str.replace('BUY', 'Buy')
        print(df_2['ticker'])
        
        for index, row in df_2.iterrows():
            try:
                Transaction.objects.create(
                    date =  datetime.date.today(),
                    order = df_2['order'] ,
                    asset = Asset.objects.get(ticker = df_2['ticker']),
                    shares_amount = df_2['shares_amount'],
                    share_cost_brl = df_2['share_cost'],
                )
            except Exception as e:
                print(f' Key Exception - {e}')
                pass

When i try to print(df_2['ticker']), i get as result a list with id(from pandas index) and ticker

0     BNB
1    LINK
2     XMR
3     DOT
4    AVAX
5    LUNA
6     SOL
7     ETH
8     BTC

i just added to the code to get the list of assets

queryset = Asset.objects.values_list("ticker")
df = pd.DataFrame(list(queryset), columns=["ticker"]) 
print(df)

the result:

   ticker
0     XRP
1     XMR
2     VET
3    USDT
4     STX
5     SOL
...
17    ETH
18    DOT
19    CRO
20    BTC
21    BNB
22   AVAX
23   ATOM
24    ADA
25   AAVE
26  1INCH

CodePudding user response：

I'm not familiar with Pandas, but for asset = Asset.objects.get(ticker = df_2['asset']), to work you need to access single ticker value out of it.

I guess it will be something like:

 asset = Asset.objects.get(ticker=df_2[row_idx]['column_name'])

for example:

asset = Asset.objects.get(ticker=df_2[0]['asset'])

CodePudding user response：

Found the error, it was how to get the single value of the field, instead of using df_2['field'] was getting the index and field, to get only the field without index i should use df_2.loc[index]['field']

Also i did some workaround to get the asset_id instead of asset

How it get in the end:

for index, row in df_2.iterrows():
            try:
                Transaction.objects.create(
                    date =  datetime.date.today(),
                    order = df_2.loc[index]['order'] ,
                    asset_id = df_3.loc[index]['id'],
                    shares_amount = df_2.loc[index]['shares_amount'],
                    share_cost_brl = df_2.loc[index]['share_cost_usd'],
                )
            except Exception as e:
                print(f' Key Exception - {e}')
                pass

And here is the workaround to get the id, don`t know if is the best solution, but i decided to go this way to make object creation more simple:

# changed the index of df_2
df_2['order'] = df_2['order'].str.replace('BUY', 'Buy')
df_2 = df_2.set_index('ticker')

# got the assets from the django app with id, set the ticker as index also and merged both tables using the ticker as index
queryset = Asset.objects.values_list('id', "ticker")
df = pd.DataFrame(list(queryset), columns=['id',"ticker"]) 
df = df.set_index('ticker')
# print(df)
df_3 = df_2.merge(df, left_on="ticker",right_on="ticker",how='inner').set_axis(df_2.index)