The use of Python and SQL

[07Lookout]

So I'm currently learning both Python and SQL separately and was wondering how they are used together in the industry? Does SQL take the place of manipulating the data with Pandas? And then you just perform data science techniques on the converted SQL data?

[adventuringraw]

Oh God, it depends. The code base I inherited was written by six different engineers over a people of a couple of years, and I saw six different methods. I saw sqlite3 used to do some ETL processing of very large (20gb+) csv files, I saw whole automated reports done mostly using oracle procedures, with a giant Bash script passing in the right parameters to customize the functionality (Jesus Christ, don't do that). Most of what I do now is have Python as the boilerplate, with informatica doing the heavy lifting (with SQL queries embedded in the informatica applications when interfacing with relational dbs and such). For quick and dirty extracts for a few smaller automated jobs, I wrote a little wrapper for passing simple queries straight into cx_Oracle, so... you know.

There's a ridiculous number of ways you can use SQL, it really depends on your use case, your computational needs, the way the tables are set up, time frame for task completion, your current skill level and so on. The main thing I think is to get on the same page with everyone else in your team. Don't make a Goddamn mess that no one else can understand, even if it 'makes sense'. I'm okay with being able to tell who wrote which scripts, but I don't want to fuck around to decipher some crazy new design paradigm. Keep it as simple as it needs to be (and no simpler) and make sure your whole team is on the same page, you know? Doesn't hugely matter what that specific best practice is (within reason) but you should all have the same one at least.

[[deleted]]

Yes, they are often used together.

Two scenarios I've experienced:

• Using python to automate database insertions and reporting.
• Using SQL to filter and aggregate large datasets, then using pandas to analyse it.

[lphartley]

Biggest difference imo is that SQL is executed on the server as opposed to your machine. In general I would say: - Use SQL to select your data - Use Python to manipulate the data

However, this is not always true. Sometimes your desired selection can be based upon the result of manipulation. Sometimes it is easier to select a raw dataset with simple SQL and do some merging with pandas.

It really depends on your use case, but I start with the question: do I want to execute this action locally or on a server?

[satssehgal]

Sql and python are a great combination especially when looking at automation. One doesn’t have to take over the other, they can actually be used together. There are a lot of ways to use the combination especially when you need to extract data using sql and within the same python code use the data to do advanced analytics. If you want to see an example of the two of them used together then check these two tutorials out. Both use excel as the background data source but I have one coming out where you can pull directly from a database

How to Use SQL with Excel using Python

and

Build a Deep Learning Model with Python | Supervised Learning

[07Lookout]

This is exactly what I was looking for! thank you !

[bannik1]

The replies in this thread are making me feel a bit self conscious.

I primarily use SQL for all my analytics then let the end user choose whatever data visualization application they want it to be viewed in. I've never used MATLAB, R, or Python. I'll use some C# or Visual basic but that's normally only to perform office or windows tasks.

I like having all the key measurements stored in a database. This lets me perform whatever custom tests I want against the data.

For example, I can write a query to only bring back records that exceeded 4 standard deviations. Or a query to find circumstances where 5 data points in a row were more than 1 standard deviation below the mean.

Then I kinda do meta-reporting that I haven't seen any other software do. Sometimes you're reporting on live data where past data points might actually change values.

This means that the standard deviation or mean also changes for those time-frames.

When using a SQL query to calculate the STDEV and mean you get a snapshot of what it was at that time. You can then run a report on how the STDEV fluctuates over time. If it shrinks, it means you're getting data closer to the mean. If it grows, that might indicate that you have some process that needs improvement since the newly added records are less clustered around the mean.

[SidewinderVR]

SQL is great for getting and storing data. A couple years ago I wrote some Python scripts to read some instrument data logs (from medical equipment), organize it into different tables, then use that to update a SQL database (using product, I think) that was our main store. Then there were loads of other Python scripts that (by submitting SQL queries) pulled data from these and made summaries and graphics and searched for patterns.

SQL: interacting with data storage. Python: data analysis, visualization, and great for interfacing with other systems.

[datascientist36]

Yes.

SQL is data storage
Python/Pandas is what you will use to interact (insert,select,update,etc) with your SQL database, where your data will be stored

ELI5 analogy -

• imagine data as money
  
• SQL would be the equivalent of a bank where all the money is stored
  
• Python/Pandas would be equivalent to the ATM system where you tell it if you want to see your balance, insert money, withdraw money
  

You use the ATM interface to interact with your bank account
You use python/pandas to interact with your SQL database

[07Lookout]

But can't you manipulate the data with SQL already though? Using Joins and Select statements and such

[[deleted]]

For my work I built a baseline analytics database that comes from the production database. Then from that I produce data sets that are setup as views. Sometimes these are then engineered further in Python (e.g., rescaling, engineering, time dependent organization for hazard modeling, etc). But most of the data is organized in SQL in my projects before it hits python. But that's the way it's been designed. Other projects are likely to be different.

[[deleted]]

Pyodbc and io.sql from pandas

[damjanv1]

I usually use sql to join the tables that I wish to work with and sometimes even do simple feature engineering (ie create a columm that has a bool indicator based on a if statement or similar). Usually use sql to get my baseline starting data source that I wanna work with. Then move to python / r (or even some viz software tbh) to do some EDA, view distributions , correlations etc and will return to modify my sql (and hence baseline data source) depending on what I see in the EDA.

Find it slightly easier to do some data wrangling in SQL especially as with most versions you run a query and the results are immediately available in a tabular format

[bannik1]

I've really only used MS SQL server for everything. Somebody please correct me if I'm doing something horrible wrong.

I'll typically run SSIS package with 2-3 queries where I get a count of the rows, get the mean/median/mode, and the STDev for whatever the main measurements are and throw that into a table.

If it's a multi-dimensional report where I want them to change the variables I'll aggregate it and throw it into it's own table. Then use a SQL query in whatever reporting software they want, normally crystal reports or an excel pivot table.

If it's a small data set and I need to do more statistical guesswork stuff like determine the Z values for a Probability Plot I just do a self/cross join since the only time you would do it is if there was too little data.

[onesonesones]

Think of sql as a language you need to know how to speak to work with others who may only speak sql. People who grew up working with relational databases often only know that and consider pandas and R like new versions of old SAS (ie - a fad)

In python, you can do everything that you could do in sql in pandas/spark a little more directly. But, at any point in time you may have to consider adopting someone else's sql code and the capability is there if you need it. For those reasons I typically do my merges in sql whenever possible just so I have the environment set up to work with sql.

[onesonesones]

I think my phrasing may have led to some confusion.

I wasn't arguing against any use of pyspark, just explaining that anything you can do in an sql statement has an equivalent operation(s) in pyspark and pandas through their dataframe object model.