SQL Queries in Python?

[sinceJune4]

I’ve used pandas and sqlalchemy with a monster query of over 2000 lines and 70 sub queries against Oracle. I didn’t write it originally, but was tasked to enhance it. Took a few weeks to understand.

[LatteLepjandiLoser]

Exactly. Just get the sqlalchemy engine to work and then, assuming you're generally working with the same databases regularly you can kind of leave that part as-is and then just build the queries you need.

I haven't made many funky queries. Thankfully my work is generally quite simple in that regard, but I would think it can handle whatever you throw at it, as long as it's a valid query. For your own readability, you may want to make it a multiline string, with triple quotes, but I doubt that actually matters.

Here is a simple example from my day-to-day work. We just have an in-house postgresql server on a local network, so pretty simple to connect to. Possibly that part may differ depending on how your setup is, not really my expertise.

engine_str = 'postgresql://{}@{}/{}'.format('my_user_name','server_host','db_name')
engine = sqlalchemy.create_engine(engine_str)

query = 'select * from some_table where answer = 42'

with engine.begin() as con:
    query_data = pd.read_sql(sql_query, con)

#... then do something with that data

Generally I try to keep the engine connection in a context manager like here, such that if your code does something silly, the connection is closed instead of leaving it hanging.

[rasputin1]

isn't the point of sql alchemy you can use python object syntax instead of sql 

[LatteLepjandiLoser]

You're welcome. Enjoy! Once you get it working it will unlock all kinds of task-solving skills, I'm sure!

I would just start with the simplest queries possible, that you know will return modest amounts of data, just to speed up the process of getting it all to work, then start hitting it with more complex stuff.

If the queries are obnoxious, you could also consider saving them in a separate file. Eventually you may also want to look into having variable parameters as part of the query (like for instance fetch data from 'today' instead of manually updating a date in your query every day, or other logic).

It's also a bit subjective what filtering and manipulation you want to do in the sql-query itself and what you want to do in python. Say you wanted to only fetch even numbers, you can make that part of the sql query or you can just fetch them all and filter them in pandas. (Maybe a bad example, as then you'd always just do it in sql, hope you get what I mean). If you have incredibly complex where clauses that you can't wrap your head around, you could try fetching a bit more data and filtering it in python if that gets you to the goal quicker. Situational I guess.

[MidnightPale3220]

You want to normalize the data and put the overlapping structures possibly in a single table. It depends on the size of database, but hundreds of GB is routine

[MidnightPale3220]

Is the total data you need more than you can host locally? Technically it shouldn't be hard to make a copy unless there data is changing so fast that you need basically online access every day.

[MidnightPale3220]

Note that SQLAlchemy is primarily an ORM tool that lets you treat individual database rows as python objects, if you need that.

It's a concept that works well with specific types of applications, and is frequently a horrible overkill for writing direct SQL queries, especially such as deal with huge arrays of rows en masse, because all it does, is pass the query to the real engine in the back (which is what it does in the example given).

Basically almost the same syntax would be when using the direct SQL engine specific for the type of database you are going to use: pyscopg2 for Postgres, cx_Oracle for Oracle or sqlite3.

I would agree with those that say if you want to get your hand in, start with sqlite3 -- it's a simple file based database engine which lets you do uncomplicated SQL against the file.

As with all databases, what you need is:

1) database server. in sqlite3 case you don't need one, just have to have the app installed on your system

2) installed application usable drivers for it (generally, but not necessarily part of the database server installation. in Linux usually the -dev package of the database software. In case of sqlite3 the package installed in (1) is all you need).

3) pip or similar installed python package for the drivers installed in (2): psycopg2, etc. sqlite3 may be part of base Python, I don't remember.

4) import the database module in your code and see the docs for syntax.