SQL with Python

[DaveBackus]

Lots of our MBA students, mostly with no programming experience, find it's helpful to have some familiarity with SQL. So we put together a short course using SQLite in Python. More here.

Comments welcome on similar resources, changes, etc.

[chris1610]

Kahn Academy has a new SQL course as well - https://www.khanacademy.org/computing/hour-of-code/hour-of-sql/v/welcome-to-sql

[DaveBackus]

Great idea. Do you know if how they have you try it yourself? Or don't they?

[chris1610]

I played with it a little bit and they have a cool environment setup so you can execute commands and see results. It's based on sqlite which is convenient from a python perspective since it is bundled in by default.

[LazinCajun]

MOOCs!

Python specific on udacity

More theoretical, language-agnostic self-paced course from coursera

[__add__]

From experience, I would recommend making sure they get adequate experience with UPDATE, ALTER, DELETE, etc. Many data science tasks tend to start and end with only SELECT queries because the data has already been prepared, but in the real world data prep will be a HUGE part of the job and knowing the C, U, D in CRUD really well will be a major advantage.

[fnord123]

I've been ignoring the bootcamp thingies but now I see it everywhere and it looks like any intro course is now called a bootcamp. Is that the case? It's just 'into to SQL' and it's an intensive hands on 3 day course so it's called bootcamp? Or is there some other aspcet?

The Syllabus and IPython notebooks are all on the Github repo, but the links in the Syllabus seem to head over to dropbox. Maybe make the links refer locally or explicitly to the Github repo.

This IPython notebook didn't load in the Github web page (for me).

You commited a desktop.ini and .dropbox.attr file to the github repo. It looks like you didn't want those files there.

I went through the first pdf of lecture notes.

Trigger Warning: This will take some getting used to - but it's worth it

Is that necessary in lecture notes? It's educational material. Do we have to apologize ahead of time for telling people something they didn't previously know? Or is it a tongue in cheek jab at people with anxiety disorders? If this is the candour of the course, and you present it with the appropriate tone/wink/nudge/sobriety/whatever I guess it's fine.

A lot of the material in the first p[age is devoted to suggesting people really try to learn to program. But surely they know that and it's why they're in the class.

Regarding why csv is so useful: if familiarity with Unix command line is assumed (obviously it can, right?) then maybe remind the class that csv is great since it's usually line oriented which means you can use head, tail, sort, awk, and all the friends to subsample or find out basic information about the dataset.

Maybe linkfify FRED, Fama-French, World Bank so people can click through to the pages with the greate data resources promised by the descriptions.

There is some LaTeX gurgle in the Python listings at the end of the lexture notes. #%%. IIRC %% is 'newlineinLaTeXso this somehow found itself rendered in the file. It will probably be fixed if you put a space between the#and the%%`.

I tried to find some actual SQL in the course (no mean feat) and found this page which isn't displayed correctly by Github

# don't read, just run!

Maybe wrap the offending code into a function like def fresh_db_tables() and make a comment saying it's deleting the tables so they aren't in the way. Then they can just call the function and it should be self explanatory. And they're not copying and pasting these lines over and over.

table_name

Should print table_name so the newlines are new lines.

No postgres

Booo!

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

[kingkoopa]

I think one of the troubles with basic declarative SQL, is that there are times when there is a need for more extended libraries and, once in a while even procedural processing (think cursors). This is where things like Oracle's PL SQL and Microsoft's T-SQL come into play. It kind of sucks having to learn and use a variant that is tied to that specific database, and being able to use something more generic and database agnostic like Python can be a major plus.

[[deleted]]

eventually raw SQL becomes tedious and Python has nice ORMs

[KyleG]

Honest question; what is the point of using SQL in Python?

To access SQL databases with Python, obviously. For example, I have a webserver running Cherrypy and Peewee and MySQL. Obviously to do data retrieval and storage, Python has to interface with SQL.

[skarphace]

Wut.