Django comes with a powerful ORM framework by which one can easily design, implement, and interact with databases. The ManyToMany relationship field provides intuitive access to semi-complex data relationships. Knowing how to leverage it within Django filters can make your queries more efficient.<\/p>\n
To illustrate the ManyToMany field in action, let’s create some simple database models representing publicly-traded securities and their historic market prices. This could be used to model price changes over a period of time for a stock.<\/p>\n
from django.db.models import Model, CharField, ManyToManyField, FloatField, DateField\r\n\r\n\r\nclass Stock(Model):\r\n \"\"\"\r\n Model for single security\r\n \"\"\"\r\n symbol = CharField(max_length=8, unique=True)\r\n prices = ManyToManyField('Price', related_name='price_stock')\r\n \r\n \r\nclass Price(Model):\r\n \"\"\"\r\n Model for historic Stock quotes\r\n \"\"\"\r\n price = FloatField()\r\n date = DateField()<\/pre>\nWith this design in place, one can query Stock objects, Price objects, or functional relationships between the two\u2014Stocks\u00a0with<\/em> Prices, for example. The use-case here is to generate a query that returns all Stock objects with at least one Price relationship. Simply put: that’s retrieving any stock that has pricing data<\/strong>.<\/p>\nExample Dataset<\/h2>\n
First, let’s generate some sample data for Stocks, Prices, and relationships between the two. There’s a lot of shortcuts in the following code, but it’s effectively creating a number of Stock entries then adding historic pricing data for a sample of those entries.<\/p>\n
# Generate random securities\r\nrandom_symbols = set([\r\n # Generate 50 random series of characters between 1 and 7 characters in length\r\n \"\".join(random.choice(string.ascii_letters).upper() for x in range(random.choice(range(1, 8)))) for y in range(50)\r\n])\r\n\r\n# Add all symbol data as Stock objects\r\nfor random_symbol in random_symbols:\r\n\r\n # Create a new stock object\r\n # Note: the use of get_or_create here negates the need for unique symbol values\r\n stock, created = Stock.objects.get_or_create(symbol=random_symbol)\r\n\r\n\r\n# Add pricing data for 10 randomly-selected symbols.\r\n# Note: Symbols omitted to demonstrate query filtering later.\r\nfor random_stock in random.sample([x for x in Stock.objects.all()], 10):\r\n \r\n # Create a new pandas dataframe with randomized pricing data for 1 years\r\n price_data = pd.DataFrame(columns=['date', 'price'])\r\n price_data['date'] = pd.date_range(start='1\/1\/2020', end='1\/1\/2021', freq='D')\r\n price_data['price'] = [random.uniform(1.5, 5.0) for date in range(price_data.shape[0])]\r\n\r\n # Create a Price entry and add as relationship to Stock object for each entry\r\n for index, row in price_data.iterrows():\r\n\r\n # Create new price entry\r\n price_entry = Price.objects.create(price=row['price'], date=row['date'])\r\n\r\n # Add relationship to stock\r\n random_stock.prices.add(price_entry)<\/pre>\nThe above code has added a year’s worth of pricing data (1\/1\/2020-1\/1\/2021) for 10 randomly-selected Stock objects from the 50 added to the database. To get an idea of what things look like:<\/p>\n
# import models\r\nfrom stocks.models import Stock, Price\r\n\r\n# Get count of total DB Stock entries\r\nStock.objects.count()\r\n>>> 50\r\n\r\n# Get count of total DB Price entries\r\nPrice.objects.count()\r\n>>> 3670<\/pre>\nQuerying for Models with ManyToMany Entries<\/h2>\n
Now that our test data is created, we can begin to approach the problem: querying for all Stock entries that have price data. In other words, we don’t want to return any Stock object without<\/em> pricing data. While Django’s ORM is incredible, this use-case can be approached via several ways\u2014each with different performances.<\/p>\n# import models\r\nfrom stocks.models import Stock, Price\r\nimport time\r\n\r\n# First Approach \u2014 doesn't return expected data\r\nst = time.time()\r\nStock.objects.filter(prices=True).count()\r\n>>> 1\r\ntime.time() - st\r\n>>> 0.003003358840942383\r\n\r\n# Second Approach - doesn't filter data\r\nst = time.time()\r\nStock.objects.exclude(prices=False).count()\r\n>>> 50\r\ntime.time() - st\r\n>>> 0.0010004043579101562\r\n\r\n# Third Approach - returns expected data super fast\r\nst = time.time()\r\nStock.objects.exclude(prices=None).count()\r\n>>> 10\r\ntime.time() - st\r\n>>> 0.0\r\n\r\n# Fourth Approach - returns expected data\r\nst = time.time()\r\nStock.objects.exclude(prices__isnull=True).count()\r\n>>> 10\r\ntime.time() - st\r\n>>> 0.0010006427764892578\r\n\r\n\r\n# Fifth Approach - returns expected data, but much more slowly\r\nst = time.time()\r\nlen([stock for stock in Stock.objects.all() if stock.prices.count() > 0])\r\n>>> 10\r\ntime.time() - st\r\n>>> 0.019017934799194336\r\n<\/pre>\nTo be honest, I’m not entirely sure why the first two examples don’t return the expected data. The syntax, while valid, doesn’t seem to produce the query I’d expect. The remaining examples, however, each return the 10 Stock objects for which pricing data was added.<\/p>\n
The Stock.objects.exclude(prices=None).count()<\/code> produces the fastest query, though admittedly there could be much more test data to magnify the differences. Aside from the illustration of effective syntax, I’d like to emphasize that remaining within the syntactic realm of Django’s Queryset is crucial to optimize query speed. In the last example using Python’s list comprehension\u2014the query is nearly 10-times as slow<\/strong>!<\/p>\nKnown Issues<\/h2>\n\n- The generation of random symbols is willy-nilly and doesn’t always produce the same number of unique values.<\/li>\n
- The time-series data doesn’t account for days that actual security wouldn’t be trading\u2014weekends, holidays, etc.<\/li>\n
- Pricing data is completely random in range and doesn’t attempt to model any type of trend (price increase, splits, etc.)<\/li>\n
- Adding this much sample data to a standard SQLite database is slow. Using another database type (like PostgreSQL<\/a>) that accommodates parallel writes, in combination with Python’s
multiprocessing<\/code> library, is recommended.<\/li>\n<\/ol>\nFinal Thoughts<\/h2>\n
I find myself using Django quite often simply for its ORM system. When I need to iterate quickly with some data that necessitates a relational design I often use while validating an idea\/notion\/curiosity. The Django ORM makes data modeling and basic querying a breeze.<\/p>\n