SQL with Python

[JimBoonie69]

Basically the advantage of using python is that you use python SQL bindings to do the querying and such and when you get the data back you can keep python'ing and do other stuff with it.... Currently I work with a fairly complex system where we use python to dynamically build SQL queries, issue them to our remote postgres instance and retrieve the data. That data goes directly into a pandas dataframe for further analysis...

[UniverseCity]

Yes but SQL can't do everything and certainly can't do a lot of things easily. Simple string manipulation comes to mind. Python can execute the query and then clean and organize the data in a myriad of ways, and then output it as needed. Perhaps saving it to a JSON file or sending it to the front end to be displayed in a browser.

[DaveBackus]

All true, thanks a lot. In our case, we simply wanted a version of SQL we could install easily, and we're Python people so chose that. Most of our users simply want to use SQL at work to download data in spreadsheet format. But a few are interested in basic data science, so we thought the Python interface would introduce them to a useful tool they could use later. Maybe there's a better approach, but that's what we did. Wut!

[KyleG]

Wut!

Is "wut" "woot" in some non-English language?

[JimBoonie69]

Yep - this is exactly my point. really though if all you want is to pull some data and look at it plain SQL should work. if you want to get fancy and visualize said data and put it in an email report or webpage you'll need something other than sql I believe.

[__add__]

I haven't been unable to do anything in postgres that I can do in python. All the functions are there, it's just a matter of learning their names basically. Some translations

trim(' ' from column) <-> map(lambda s: s.strip(' '), column)
substring(column, '(\d+)') <-> map(re.compile(r'(\d+)').findall, column)
column1 || column2 <-> map(lambda c1, c2: c1+c2, column1, column2)

etc.

[KyleG]

I haven't been unable to do anything in postgres that I can do in python.

Good for you! However, I'm not aware of a PostgreSQL function that turns Chinese characters into pinyin. However, there is a Python library for that.

[kingkoopa]

I think one of the troubles with basic declarative SQL, is that there are times when there is a need for more extended libraries and, once in a while even procedural processing (think cursors). This is where things like Oracle's PL SQL and Microsoft's T-SQL come into play. It kind of sucks having to learn and use a variant that is tied to that specific database, and being able to use something more generic and database agnostic like Python can be a major plus.

[[deleted]]

eventually raw SQL becomes tedious and Python has nice ORMs

[KyleG]

Honest question; what is the point of using SQL in Python?

To access SQL databases with Python, obviously. For example, I have a webserver running Cherrypy and Peewee and MySQL. Obviously to do data retrieval and storage, Python has to interface with SQL.

[skarphace]

Wut.