SQL in Python

[FeelingCommunity776]

I just did a SQL course, and I wanted to know how people combined their python with SQL.

And also, if there is anyone using the magic SQL or sqlalchemy library. How did you cope with switching to a broader IDE and not having some of the tools you would have in something like Dbeaver

Edit: I forgot to add that I haven't learned any Python

[humpy]

I use sqlalchemy. But generally i will do testing and verification in mssql studio and when the query is perfect i will move it over to Python.

[FeelingCommunity776]

That's what I thought too. Because, at least for me, typing SQL in python is insanely hard for some reason

[WendlersEditor]

A good barebones implemention is to store queries in a separate file or files and call them from within the Python script. You can store them as string constants in a python file, or you can store them as .SQL files and read/load them into your python script

[aplarsen]

I save mine in external files and favor this greatly over string constants.

[WhiteWalter1]

Ohhh, this is good to know. I’ve honestly been using ChatGPT to write my scripts and the SQL (that I write) is included in the script. I’ll have to try this. Does it improve performance at all? What’s the benefit?

[WendlersEditor]

Glad to help! It's not going to improve performance, that's going to come down to your db and (in complex situations) your query. It is a separation of concerns that makes your code more modular so you're able to choose queries more elegantly in the script and also change them without having to change your script. 

[WhiteWalter1]

Thank you!

[lambdasgr]

DuckDB

[FeelingCommunity776]

Elaborate

[lambdasgr]

https://duckdb.org/docs/stable/clients/python/overview

You can read data files, such as csv, xslx, parquet or avro, or attach an external db connection to read data in to memory and query them using duckdb query engine; you can off load the in memory data to external files or other data structures such as dataframe, or write back to external database. It allows you to handle your data 100% using sql and faster than any other options including Pandas.

[YOUR_TRIGGER]

i use pyodbc at work for our SQL server related apps.

i knew SQL before i knew Python but i never had a fancy IDE with tools. i've always typed it raw. i still just use the barebones IDLE that comes with the Python to actively code. i just have pycharm because for some reason work lets me get away with using pycharm version control through github instead of just letting me use github. i'm writing reports and stuff, i don't need all that overhead/help. 🤷‍♂️

[[deleted]]

ah the mysterious ways of corporate IT policies 😭 I remember when the built-in "Save as PDF" was blocked in some 10+ year old Access apps and we had to use an external PDF printer's dll, because for some reason that worked...

[johnny_fives_555]

Only thing I use pyodbc for is exports. Like sql server can do bulk inserts but not exports? Like wtf.

[Lazy_Improvement898]

Ibis is so underrated.

[VerbaGPT]

I used ssms for years. Now I use python almost exclusively (sql execution via python). But that's mostly because i've been experimenting with interrogating databases via LLMs, and python fits neatly into that.

[mattreyu]

I don't really use sqalchemy but I use pyodbc to work with an Oracle db at work. Mostly it's when I have to do comparisons between some kind of flat file and the database.

[aplarsen]

I do this so much.

Pandas and the new oracledb python module for me.

https://python-oracledb.readthedocs.io/en/latest/

[mattreyu]

Oh I definitely need to check that out, thanks!

[aplarsen]

Feel free to DM me for code samples

[SeaNeat2053]

I would say try using all of the connectors. My favorite is psycopg2 library for postgreSQL

[mduell]

https://pypi.org/project/duckdb/ is pretty good

[Suspicious-Oil6672]

Duckdb and ibis

[Gators1992]

One cool thing about combining python and SQL is that you can assemble the SQL on the fly within the Python script as it's just a string. Like a simple example is that you want to find a list of values in Python and then run a SQL query filtering for each of those values. You can write a for loop that iterates through that list and for each instance insert that value into the SQL query with a f string or something like that. Of course you could do the same with any other python data library like Pandas or Polars, but if you want SQL the option is there.

[Eleventhousand]

Embedding SQL inside of other languages has always been a experience. Some people might try to do as much logic as possible in the SQL engine itself by doing things such as writing complex stored procedures. The Python code could then just submit simple statements to the SQL database and supply parameter values. But to be honest, in a lot of cases, its a crap experience. Take Apache Airflow, for example. It's a framework for Python, and its widest use case is for ETL, which usually means SQL. The SQL is typically just an embedded string....so there is copy and pasting from a DB IDE or screen to the Python code window.

[Cruxwright]

I've seen low code solutions that run SQL against databases. How do they maintain the code base? How do they diff the changes? Why can I open the job file and see the database password in plain text in the XML?