mlnsio/text2sql

1import uuid2from django.db import models, connection3 4 5class BaseModel(models.Model):6 7    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)8    created_at = models.DateTimeField(auto_now_add=True, null=True, blank=True)9    updated_at = models.DateTimeField(auto_now=True, null=True, blank=True)10 11    class Meta:12        abstract = True13 14 15class MutualFund(BaseModel):16    """17    This model will store the mutual fund data18    """19 20    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)21    fund_name = models.CharField(max_length=200, unique=True)22    isin_number = models.CharField(max_length=50, unique=True, null=True)23    security_id = models.CharField(max_length=50, unique=True)24    data = models.JSONField(null=True)25    rank = models.IntegerField(unique=True, null=True)26    crisil_rank = models.IntegerField(null=True)27    aum = models.FloatField(null=True)28    expense_ratio = models.FloatField(null=True, blank=True)29    return_m12 = models.FloatField(null=True, blank=True)30    nav = models.FloatField(null=True, blank=True)31 32    @staticmethod33    def execute_raw_sql_query(sql_query):34        with connection.cursor() as cursor:35            cursor.execute(sql_query)36            columns = [col[0] for col in cursor.description]37            results = [dict(zip(columns, row)) for row in cursor.fetchall()]38 39        return results40 41    @classmethod42    def execute_query(cls, query):43        try:44            return cls.execute_raw_sql_query(query)45        except Exception as e:46            print("Error: ", repr(e))47            return []48 49class Stock(BaseModel):50    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)51    name = models.CharField(max_length=200)52    ltp = models.CharField(max_length=50, null=True)53    percentage_change = models.CharField(max_length=50, null=True)54    price_change = models.CharField(max_length=50, null=True)55    link = models.URLField(max_length=200, null=True)56    volume = models.CharField(max_length=50, null=True)57    data = models.JSONField(null=True)58    isin_number = models.CharField(max_length=50, unique=True, null=True)59    rank = models.IntegerField(unique=True, null=True)60 61 62class MFHoldings(models.Model):63    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)64    isin_number = models.CharField(max_length=20, null=True, blank=True)65    security_id = models.CharField(max_length=20, null=True, blank=True)66    sector = models.CharField(max_length=50, null=True, blank=True)67    country = models.CharField(max_length=50, null=True, blank=True)68    currency = models.CharField(max_length=100, null=True, blank=True)69    weighting = models.FloatField(null=True, blank=True)70    sector_code = models.CharField(max_length=100, null=True, blank=True)71    holding_type = models.CharField(max_length=100, null=True, blank=True)72    market_value = models.FloatField(null=True, blank=True)73    stock_rating = models.CharField(max_length=100, null=True, blank=True)74    total_assets = models.FloatField(null=True, blank=True)75    currency_name = models.CharField(max_length=150, null=True, blank=True)76    holding_name = models.CharField(max_length=100, null=True, blank=True)77    holding_type = models.CharField(max_length=100, null=True, blank=True)78    holding_type_id = models.CharField(max_length=100, null=True, blank=True)79    number_of_shares = models.FloatField(null=True, blank=True)80    one_year_return = models.FloatField(null=True, blank=True)81    mutual_fund = models.ForeignKey(MutualFund, on_delete=models.CASCADE)82 83    def __str__(self):84        return f"{self.ticker} - {self.securityName}"85 86 87class MFVolatility(models.Model):88    VOLATILITY_CHOICES = (89        (1, "for1Year"),90        (3, "for3Year"),91        (5, "for5Year"),92        (10, "for10Year"),93        (15, "for15Year"),94    )95    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)96    mutual_fund = models.ForeignKey(MutualFund, on_delete=models.CASCADE)97    year = models.CharField(max_length=100, choices=VOLATILITY_CHOICES)98    alpha = models.FloatField(null=True, blank=True)99    beta = models.FloatField(null=True, blank=True)100    sharpe_ratio = models.FloatField(null=True, blank=True)101    standard_deviation = models.FloatField(null=True, blank=True)102