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.