HN Explorer

Looking to learn: SQL in MySQL and SQLlite

9 points by LookingToLearn ↗ HN
Hi,

I'm a long time programmer in C, Python, and a range of other languages. The need has arisen for me to write something that uses a database, and I'm looking for advice on where to start.

A database consists of a collection of tables, and SQL is the language used to create, populate, manipulate, query and destroy the tables and their contents.

Can anyone suggest a non-patronising, information dense tutorial or "How To" guide? Everything I've found from fairly comprehensive searching seems fluffy or way too caught up in its own gravitas.

Suggestions welcome, and thanks in advance.

8 comments

[ 2.3 ms ] story [ 34.8 ms ] thread
drpancake
I'd recommend just getting on with it. MySQL is the obvious first choice as it's widespread and well documented. Get it running locally then install the MySQLdb package for Python.

Note that in practice SQL is a small subset of the languages accepted by MySQL, PostgreSQL, SQLite, Oracle et al. Each defines it's own custom dialect but the concepts are mostly universal.

At first I would use the 'mysql' command-line utility so you can see exactly what's going on; this comes bundled with the installer. Have a think about how your data would fit into rows and columns, but don't over-analyse too much - it takes practice to start thinking in a relational way.

Create your tables and insert some example data. Now in Python connect to your local instance and try querying for some rows (hint: DictCursor is useful, albeit wasteful if you care about performance).

Now read about database normalization and transactions. You should be well on your way!

LookingToLearn
You'd recommend MySQL over SQLite? The latter comes "baked in" to Python, so no installation required, and I was planning to go with that as a starter to avoid any problems with installation and configuring.

It would be easy to write a command line for that so I could then experiment. Thoughts?

And any suggestions for guide to SQL syntax and semantics?

Many thanks.

drpancake
Good point - SQLite is even easier to get started with. I believe its command-line tool comes bundled on most distros as 'sqlite3'.

As for syntax and semantics... it's so simple that anything on Google will do. The interesting part is normalization - again I'm sure there's plenty of free guides.

LookingToLearn
I'm pretty sure normalization won't be a problem in my context and with my background. My requirement is simple, and the design straight-forward.

Many thanks for your response.

drpancake
When I say normalization that includes simple cases. For example using foreign keys to separate out your data - pretty common in most schemas.

Good luck!

andrewcooke
sqlite is nice because it's so easy to use but it's a pig to optimize and isn't a complete sql (it lacks typing, foreign keys are not enforced, etc etc). despite that i would (do!) use it for projects where you're storing data for one program (configuration, etc).

but if the main functionality of your program is storing data then you are better with something a bit more comprehensive. to be honest, i would suggest postgres over mysql, but either will do. not only as these more feature-complete, but they are much easier to optimise.

also, if you are using python, you might consider sqlalchemy. that can be used as an orm, but is really more like a very neat embedding of sql in python. and it will even let you define your database in python. i think it's very cool. but there's also a risk that you might end up confused about what is sqlalchemy, what is python, and what is sql.

finally, python's documentation for calling sql is not that great. you'll probably end up wanting to read this document - http://www.python.org/dev/peps/pep-0249/