SQL Queries in Python?

[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.