5 ms·

I personally think that SQL and tabular data should be built into languages in a manner similar to text and regular expressions. I get his (partially) in python

by geebee 5y ago

I personally think that SQL and tabular data should be built into languages in a manner similar to text and regular expressions. I get his (partially) in python by using pandas and pandasql, which (to my understanding) initiates sqlite in the background. There are a few other modules that do this as well. Recreating relational table query operations as a set of operations unique to a specific programming language instead of integrating SQL makes, to me, as much conceptual sense as recreating a unique text pattern matching search for each language rather than integrating regular expressions. This is only a small sliver of the topic here, but I do think that SQLite is probably a good backend for this.

weird-eye-issue 5y ago
That's basically what an ORM is. In fact Django uses SQLite out of the box and their ORM overrides lots of Python's operators so there we are!
- geebee 5y ago
  I haven't used an ORM for some time (back when I programmed in Rails), but my experience and understanding is that an ORM largely replaces hand written SQL for a number of CRUD-style queries to manage object relational mapping for a persistence tier, and addresses a different issue than native vs SQL based operations in tabular environment such as as Pandas or R-dataframes. What I'm talking about is this: https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/getting_started... I personally am not especially interested in recreating relational set operation using pandas operations. There are things I'd much rather do in pandas (such as summary statistics), and some things I'd much rather do in SQL elaborate JOINs and aggregations. I do admit there will be a grey area. Interestingly, there are a lot of people who absolutely can't stand SQL, whereas I (and a lot of people) vastly prefer it. As for ORMs - I actually did like them back when I did this sort of programming (managing the back and forth between objects and tables was honestly very boring), though once I was into reports, I often went straight to raw SQL. I no longer do that sort of work, and almost all code I now write is for analytical purposes, so I don't do any CRUD.
  - weird-eye-issue 5y ago
    I have used pandas and the way it is used in Python is quite similar to how you would interface with the Django ORM. Btw I've written lots of analytical types of queries using Django ORM (to power the backend for a dashboard API, for example). It is quite powerful. You can even do window functions and such directly with the ORM.
    - geebee 5y ago
      I'm having a little trouble understanding this - are you writing SQL to do the window function, or does the ORM provide a non-sql way to do the window function?
      - weird-eye-issue 5y ago
        I'm using the ORM to do a window function, without writing any SQL directly. In fact I've done queries with several window functions, aggregations, and joins in a single query all from the ORM without writing any SQL. It is much easier to read and maintain than raw SQL too imo.
        geebee 5y ago
        Ah. Well, that clearly works for a lot of people and sounds similar to how pandas works, though it's actually the opposite of what I'm describing here, which is the option to write SQL directly against a tabular data frames. For now, this is possible in Python through what I'd describe as out of the mainstream open source modules that are reputable and written by good programmers but may not be actively maintained. I get the impression that a sqldf in R is a bit more mainstream among R programmers, though I'm not sure of this.
Someone1234 5y ago
I'm not sure I completely get what you're after. So let's say a concept of SQL was "built-in," the language now understands the syntax: So what? Without access to the underlying data the SQL is about it is just as meaningless as a raw string (i.e. you cannot know if a query is valid without the underlying data to validate it against). If your language now needs a persistent connection to some underlying SQL data-source (with all the problems that entails) building it into the language is barely better than just executing SQL during your tests. So I'm not really sure what you want to accomplish or what value you believe this would add.
- geebee 5y ago
  I think the link I put below probably does a better job explaining what I'm looking for. This has mainly to do with dataframes (not unstructured text). Here's the link again in case this thread gets long and you don't know what I'm referring to: https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/getting_started... Generally, I vastly prefer the SQL operations to the pandas ones, though (and this is very important) only when pandas is essentially recreating what is in SQL's sweet spot. For example, you can use pandas operations to do joins, aggregations, filters, and so forth. I would rather write that code in sql. I would not prefer to generate summary statistics in SQL, find correlations between columns, or do other things that are in the realm of scientific or statistical programming. There will be a grey area in there, for sure. I also find that many things that require SQL trickery (such as self-joins) often have a very very simple pandas solution such was cumulative sums on a column. So I go back and forth between SQL and pandas quit a bit (as each operation returns a data frame). Just to be clear again, some people just can't stand SQL and want to stay away from it as much as possible. Other people, like me, greatly prefer it, but even for us there are scenarios where we'd much rather use pandas than get into leetcode style SQL trickery.
city41 5y ago
That's basically what linq is. It's of course not identical to sql, but one big reason for that is sql is difficult to autocomplete. Linq purposely flipped things around to help with that (ie 'from foo select something' instead of 'select something from foo')

by geebee 5y ago

weird-eye-issue 5y ago

That's basically what an ORM is. In fact Django uses SQLite out of the box and their ORM overrides lots of Python's operators so there we are!

geebee 5y ago
I haven't used an ORM for some time (back when I programmed in Rails), but my experience and understanding is that an ORM largely replaces hand written SQL for a number of CRUD-style queries to manage object relational mapping for a persistence tier, and addresses a different issue than native vs SQL based operations in tabular environment such as as Pandas or R-dataframes. What I'm talking about is this: https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/getting_started... I personally am not especially interested in recreating relational set operation using pandas operations. There are things I'd much rather do in pandas (such as summary statistics), and some things I'd much rather do in SQL elaborate JOINs and aggregations. I do admit there will be a grey area. Interestingly, there are a lot of people who absolutely can't stand SQL, whereas I (and a lot of people) vastly prefer it. As for ORMs - I actually did like them back when I did this sort of programming (managing the back and forth between objects and tables was honestly very boring), though once I was into reports, I often went straight to raw SQL. I no longer do that sort of work, and almost all code I now write is for analytical purposes, so I don't do any CRUD.
- weird-eye-issue 5y ago
  I have used pandas and the way it is used in Python is quite similar to how you would interface with the Django ORM. Btw I've written lots of analytical types of queries using Django ORM (to power the backend for a dashboard API, for example). It is quite powerful. You can even do window functions and such directly with the ORM.
  - geebee 5y ago
    I'm having a little trouble understanding this - are you writing SQL to do the window function, or does the ORM provide a non-sql way to do the window function?
    - weird-eye-issue 5y ago
      I'm using the ORM to do a window function, without writing any SQL directly. In fact I've done queries with several window functions, aggregations, and joins in a single query all from the ORM without writing any SQL. It is much easier to read and maintain than raw SQL too imo.
      - geebee 5y ago
        Ah. Well, that clearly works for a lot of people and sounds similar to how pandas works, though it's actually the opposite of what I'm describing here, which is the option to write SQL directly against a tabular data frames. For now, this is possible in Python through what I'd describe as out of the mainstream open source modules that are reputable and written by good programmers but may not be actively maintained. I get the impression that a sqldf in R is a bit more mainstream among R programmers, though I'm not sure of this.

Someone1234 5y ago

I'm not sure I completely get what you're after. So let's say a concept of SQL was "built-in," the language now understands the syntax: So what? Without access to the underlying data the SQL is about it is just as meaningless as a raw string (i.e. you cannot know if a query is valid without the underlying data to validate it against). If your language now needs a persistent connection to some underlying SQL data-source (with all the problems that entails) building it into the language is barely better than just executing SQL during your tests. So I'm not really sure what you want to accomplish or what value you believe this would add.

geebee 5y ago
I think the link I put below probably does a better job explaining what I'm looking for. This has mainly to do with dataframes (not unstructured text). Here's the link again in case this thread gets long and you don't know what I'm referring to: https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/getting_started... Generally, I vastly prefer the SQL operations to the pandas ones, though (and this is very important) only when pandas is essentially recreating what is in SQL's sweet spot. For example, you can use pandas operations to do joins, aggregations, filters, and so forth. I would rather write that code in sql. I would not prefer to generate summary statistics in SQL, find correlations between columns, or do other things that are in the realm of scientific or statistical programming. There will be a grey area in there, for sure. I also find that many things that require SQL trickery (such as self-joins) often have a very very simple pandas solution such was cumulative sums on a column. So I go back and forth between SQL and pandas quit a bit (as each operation returns a data frame). Just to be clear again, some people just can't stand SQL and want to stay away from it as much as possible. Other people, like me, greatly prefer it, but even for us there are scenarios where we'd much rather use pandas than get into leetcode style SQL trickery.

city41 5y ago

That's basically what linq is. It's of course not identical to sql, but one big reason for that is sql is difficult to autocomplete. Linq purposely flipped things around to help with that (ie 'from foo select something' instead of 'select something from foo')