Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
 
  • Post
  • Reply
Data Graham
Dec 28, 2009

📈📊🍪😋



^^^ I've seen/used the above pattern a few times. It can work well, but without knowing the OP's project details what I would be cautioning against is having your local/dev/staging/prod environments be TOO wildly different unless it really truly makes sense.

I keep all env-specific settings in a YAML or dotenv file that is read into settings.py and overrides any hard-coded settings, and then make sure that YAML/dotenv (which has all secrets/keys/sensitive strings) is not kept in the code repo.

That way I only ever have one settings.py, which I find a lot more maintainable than having to keep separate ones for every env.

If you have to split up the settings.py's per-env, go for it, but from my own experience I've found it just muddles things up and encourages risky behavior (letting env-specific settings slip into the repo, or letting your code repo become less portable / only applicable to your very specific infrastructure).

Adbot
ADBOT LOVES YOU

fletcher
Jun 27, 2003

ken park is my favorite movie

Cybernetic Crumb

lazerwolf posted:

I will organize my settings files in a folder called settings with a pattern like:

code:
settings
- __init__.py  # this is empty
- base.py
- local.py
- staging.py
- production.py
Base contains general settings across all environments. Then I separate out environment specific settings in each other file.

This is the way

Dominoes
Sep 20, 2007

death cob for cutie posted:

I'm helping to take over Votefinder, a Django project for keeping track of voting in games of Mafia on the forums.
That virtue signalling is kind of scummy

Hed
Mar 31, 2004

Fun Shoe
Are there any good guides (outside the docs) for powering up my queries? I've never been able to "bridge" the Django and SQL worlds well, probably because my SQL skills are so crummy.


One example is if I have

Python code:
class DataSource(models.Model):
    vendor = models.CharField(max_length=50)
    name = models.CharField(max_length=50)

class Entry(models.Model):
   source = models.ForeignKey(DataSource, on_delete=models.CASCADE)
   reference_date = models.DateField(blank=False)
   settle = DecimalField(max_digits=19)
If I wanted to grab all the Entries between dates but want to prefer "FINAL" over "INTRADAY" data I want to do something like

entries = Entry.objects.filter(reference_date__gte=start, reference_date__lte=end) and but only unique days and prefer .endswith("FINAL"). In pure SQL (I'm using postgres) I could do some sort of CASE and enumerate my values.

Another would be to choose the "closest" settle to a given number, so I could return the Entry that has the settle for a given date that is closest to what I enter. In postgres raw I could do some ORDER BY ABS() with preconditions to get a reasonably fast query. Turning that into a QuerySet query is a little odd to me.

Hadlock
Nov 9, 2004

ChatGPT is alarmingly good at writing SQL

duck monster
Dec 15, 2004

Hadlock posted:

ChatGPT is alarmingly good at writing SQL

Yeah, I'm not surprised though. SQL is fairly easy once you got your head around the core concept of relational,. I think it pretty neatly fits into how it thinks. Just make sure you double check the poo poo out of it when doing anything that can mutate the store. Its good, but its fallible

TheFluff
Dec 13, 2006

FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

Hed posted:

I've never been able to "bridge" the Django and SQL worlds well, probably because my SQL skills are so crummy.

It's not, it's because Django's QuerySet API just doesn't translate well to SQL. You can do some moderately complex things if you try hard enough but in many cases if you wanna actually use SQL's power you gotta write raw queries as strings which sucks. ORM's are always like this, the API is just the wrong abstraction. If you're a SQL-knower what you want instead is a query builder that directly manipulates SQL syntax fragments like SQLAlchemy can. I complained about ORM's in the SQL thread about a year ago.

In your first example the "prefer final" could maybe be done with select distinct on (reference_date) reference_date, name ... order by reference_date, name and Django may be able to generate that without too much fuss. In pure SQL you could just mess around with CTE's or lateral joins or whatever but not in Django.

The second one should be possible I think with the strategy you describe, you can add SQL expressions to querysets IIRC, but every time I've tried to generate something moderately weird with Django it just becomes really ugly and figuring out the correct syntax takes ages.

duck monster
Dec 15, 2004

TheFluff posted:

It's not, it's because Django's QuerySet API just doesn't translate well to SQL. You can do some moderately complex things if you try hard enough

Oh not realy. Get your head around Q expressions, and how Djangos lazy vs eager realtional stuff actually works and you can get a pretty good intuition to how the Queryset api maps to SQL and vice versa.

TheFluff
Dec 13, 2006

FRIENDS, LISTEN TO ME
I AM A SEAGULL
OF WEALTH AND TASTE

duck monster posted:

Oh not realy. Get your head around Q expressions, and how Djangos lazy vs eager realtional stuff actually works and you can get a pretty good intuition to how the Queryset api maps to SQL and vice versa.

I've done it extensively and it sucks, sorry. Even when you can accomplish what you want, it usually gets really hard to read. Like, sure, you can do a semi-join with where exists(<subquery>), but it just looks awful. And yeah, you can bend over backwards and sprinkle .annotate() and .aggregate() all over the place but it gets extremely cryptic and difficult to understand what the SQL would look like (multiple aggregations in the same query almost never do what you want and you can't access postgres' sum() filter(where <condition>) either). There is just not enough control over joins in general. If you want to write even moderately complex queries where you start using CTE's to make your subqueries more readable, it's time to give up. God forbid you want to do something more elaborate like do a lateral join, use a window function or join on a function (in postgres, cross join generate_series(...) is very useful for various daily/monthly reporting nonsense). To add insult to injury, even if you can generate the query, mapping the result back to a useful data structure that isn't a model class is just something you have to do yourself.

I don't actually dislike Django in general, though. I worked with it for years and most of the time it's a highly productive environment. I criticize it harshly in this specific area precisely because I've tried really hard to push it to its limits. Still, it has some immense strengths, it's incredibly mature, and in many areas other than query generation it's very well designed. I've missed so much stuff Django just comes with as a builtin in other languages and frameworks. I've used Django's transaction manager as a template for implementing something similar in Golang, and I've pointed to the way Django explicitly declares migration dependencies as The Right Thing To Do in various internet arguments (even major players like Flyway don't get that bit right). The query generation is really good enough for probably a good 80% of the queries you typically write, and it's pretty easy to use. When you do learn SQL really well though and you want to really leverage all of the neat things postgres can do, Django just isn't the right tool for the job. Every ORM has the same problem, Django maybe to a slightly lesser extent than many others because it's been around for so long, but it's still the same fundamental problem.

Hed
Mar 31, 2004

Fun Shoe
I feel like I'm missing something really simple, but I haven't had to do in Django before.

Here's my models:
Python code:
class Contract(models.Model):
    commodity = models.ForeignKey(Commodity, on_delete=models.CASCADE)
    strike_price = MoneyField(max_digits=19, decimal_places=4, default_currency="USD")
    type = models.CharField(max_length=1, choices=ContractTypes.choices)

    class Meta:
        constraints = [
            # only one contract per commodity/type/strike_price
            models.UniqueConstraint(
                name="%(app_label)s_%(class)s_commodity_type_strike_price_unique",
                fields=("commodity", "strike_price", "type", ),
            ),
        ]

class MarketData(models.Model):
    reference_date = models.DateField(
        blank=False, help_text="The entry date for the data."
    )
    contract = models.ForeignKey(
        Contract, on_delete=models.CASCADE
    )
    price = MoneyField(max_digits=19, decimal_places=4, default_currency="USD")


    class Meta:
        # unique_together (reference_date, contract)
        constraints = [
            models.UniqueConstraint(
                name="%(app_label)s_%(class)s_date_and_contract_unique",
                fields=("reference_date", "contract", ),
            ),
        ]
I'm trying to ingest a shitton of tabular data into a normalized database. Each row has prices for each reference_date, for each Contract. I have a Contract model which has a commodity, type and strike_price. The price data will go a MarketData model, which refers to a Contract and provides the prices for each reference_date.

I am trying to speed up ingest by using bulk_create, but I need to bulk_create() the Contracts first, then I can bulk_create the MarketData and populate the PKs directly.

That's where I'm at: Iterator sends me a block of rows with the same contracts, I want to create them if they don't exist and then populate the MarketData using the PKs from the bulk_create() step. There's a shot I already have an entry for the Contract in my database, so I've been using ignore_conflicts=True in bulk_create(), which doesn't return PKs. So I can't just use the PKs and return the rows.

So now I'm trying to bulk_create(ignore_conflicts=True) to guarantee they exist and then go query to get PKs that match, but is there a bulk_get() or something where I can just send a list of unique (commodity, type, strike_price) tuples and get back PKs such as with .values('pk')? Then I could do a python zip(rows, contract_ids) and bulk_create my MarketData and be off to the races.

Am I thinking about this the wrong way? Moving from the naive method to using bulk_create() is around a 30x speedup so I'd really like to get this to work.

edit: added constraints on models to make clearer

Hed fucked around with this message at 14:28 on Oct 17, 2023

fisting by many
Dec 25, 2009



Er which part is meant to be unique? I don't see any unique constraint so presumably Django is happy to add duplicate data, and ignore_conflicts=True is only suppressing other errors (like trying to reference FKs that don't exist yet?)

Hed
Mar 31, 2004

Fun Shoe

fisting by many posted:

Er which part is meant to be unique? I don't see any unique constraint so presumably Django is happy to add duplicate data, and ignore_conflicts=True is only suppressing other errors (like trying to reference FKs that don't exist yet?)

I updated my post. I simplified the models for posting and forgot those important parts!

Hed
Mar 31, 2004

Fun Shoe
I figured it out... use filters with in_bulk() to evaluate the queryset in 1 SQL query, then your subsequent .get() shouldn't hit the DB.

duck monster
Dec 15, 2004

TheFluff posted:

I've done it extensively and it sucks, sorry. Even when you can accomplish what you want, it usually gets really hard to read. Like, sure, you can do a semi-join with where exists(<subquery>), but it just looks awful. And yeah, you can bend over backwards and sprinkle .annotate() and .aggregate() all over the place but it gets extremely cryptic and difficult to understand what the SQL would look like (multiple aggregations in the same query almost never do what you want and you can't access postgres' sum() filter(where <condition>) either). There is just not enough control over joins in general. If you want to write even moderately complex queries where you start using CTE's to make your subqueries more readable, it's time to give up. God forbid you want to do something more elaborate like do a lateral join, use a window function or join on a function (in postgres, cross join generate_series(...) is very useful for various daily/monthly reporting nonsense). To add insult to injury, even if you can generate the query, mapping the result back to a useful data structure that isn't a model class is just something you have to do yourself.

I don't actually dislike Django in general, though. I worked with it for years and most of the time it's a highly productive environment. I criticize it harshly in this specific area precisely because I've tried really hard to push it to its limits. Still, it has some immense strengths, it's incredibly mature, and in many areas other than query generation it's very well designed. I've missed so much stuff Django just comes with as a builtin in other languages and frameworks. I've used Django's transaction manager as a template for implementing something similar in Golang, and I've pointed to the way Django explicitly declares migration dependencies as The Right Thing To Do in various internet arguments (even major players like Flyway don't get that bit right). The query generation is really good enough for probably a good 80% of the queries you typically write, and it's pretty easy to use. When you do learn SQL really well though and you want to really leverage all of the neat things postgres can do, Django just isn't the right tool for the job. Every ORM has the same problem, Django maybe to a slightly lesser extent than many others because it's been around for so long, but it's still the same fundamental problem.

I'd argue its a good ORM, because there isn't a better ORM. There really isn't, and used most of them. Like, yeah, of course certain types of queries wont map well, theres a lot of historically ink spilled on the Object/Relational impedance mismatch. But for 90% of use cases, including moderately complicated (say, across three or four tables) joins, djangos ORM is pretty drat neat.

Jose Cuervo
Aug 25, 2004
Hope it is ok to post this question here, happy to be pointed to a more relevant thread if need be.

I am building a small website using Flask and in one HTML page I need to include javascript files depending on the report type selected by the user. This is my code using Jinja2 (using images because SA was blocking me posting the code):
https://imageupload.io/kKoVPz0GQPPzZ7D

The resulting HTML code is
https://imageupload.io/zh4Y2WMLelxIqkl

I believe the code does not work because I am nesting the "{{ }}" (once around the url_for function and once around the jsFilename). Is this the issue? If this is the issue, how do I deal with it?

minato
Jun 7, 2004

cutty cain't hang, say 7-up.
Taco Defender

Jose Cuervo posted:

I believe the code does not work because I am nesting the "{{ }}" (once around the url_for function and once around the jsFilename). Is this the issue? If this is the issue, how do I deal with it?

Try:
code:
... src={{ url_for('static', filenamt='js/reports/' ~ jsFilename ~ '.js') }}
and maybe this will work too:
code:
... src={{ url_for('static', filenamt='js/reports/' ~ (jsFilename | safe) ~ '.js') }}

Jose Cuervo
Aug 25, 2004

minato posted:

Try:
code:
... src={{ url_for('static', filenamt='js/reports/' ~ jsFilename ~ '.js') }}
and maybe this will work too:
code:
... src={{ url_for('static', filenamt='js/reports/' ~ (jsFilename | safe) ~ '.js') }}

Both versions worked! Do you mind pointing me to some documentation or tell me what keyword to use to find the ~ as what to use? EDIT: I found it here: https://jinja.palletsprojects.com/en/3.1.x/templates/ under the "Other Operators". I definitely missed that when I was looking through the documentation.

I also ended up finding this solution which would have worked for my use case: https://stackoverflow.com/a/19511353/3130499

Jose Cuervo fucked around with this message at 16:40 on Oct 19, 2023

Adbot
ADBOT LOVES YOU

Jose Cuervo
Aug 25, 2004
Mistaken post.

  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply