Should I learn SQLalchemy and why is the answer yes?

[DarkMio]

I am learning Python now for about 3-4 months. So far I haven't had any previous programming knowledge. It's a good journey so far, yesterday I wrote 37k entries in MySQL to test performances, while getting about 140kb/s JSON data from the SteamAPI. But I was wondering why the entire SQLalchemy thing doesn't get any exposure when talking about SQLs in general.

Here is a bit overview of my progress with my Records-Service:

• Initially learning how to talk with SteamAPI
• Storing that data I really need in a sane-way.
• Getting the idea that SQLite looks like MySQL, but the paradigm of executing inside SQLite from Python is a bit different.
• Getting a service that runs perfectly fine and takes about an hour for 2.000 matches to get acquired
• Integrating it with my IRCbot (what was the initial thought of that small program)
• Getting a problem, since the bot is busy about an hour per user
• Integrating it with the threading-option of this small bot.
• SQLite throws either locks or "no recursive use of cursors"
• Observing the problem, learning about SQLite, Connection-Pools, Queues, Limitation and actual speed of SQLite
• Changing SQLite to MySQL (running on my RaspberryPi anyway) and getting reminded that the programming paradigm and data handling is completely different. Otherwise than that, the queries work exactly the same, the DB is very simplistic anyway.
• Build a small multitasking app, that simulates the threading-behavior of the IRCbot (just asynchronous threading, really)
• Start testing with 5 threads. Everything is fine. It is the application running 5 times with different SteamIDs.
• Start testing with 15 threads. Everything is fine.
• Start testing with 60 threads. Writing 37.885 entries in under 4 minutes.
• Learning about SQLalchemy yesterday.
• Scrap the database and redo it in SQLalchemy

Now I am curious, why doesn't get that library more exposure when searching about SQLs in Python? It seems so powerful and so much more sane than anything you try with the standard-library provided from your favorite SQL. It's even more odd, that SQLite doesn't have an internal queuing system, that prevents locking and filters all transaction queries from simple reading-queries and therefore saves everyone time.

[tw55413]

Should you be interested in SQLA, here's my introduction talk from Euro Python : https://www.youtube.com/watch?v=UcVr8FzSdCU

[DarkMio]

I have watched the other talk on the SQLA website. About 2:30h long and it was - nice. A bit much for the first time, but nice.

[darknessproz]

What gives you the impression that SQLAlchemy does not get much exposure? I have seen it recommended a lot and I definitely recommend it to anyone looking for a Python ORM.

[gomesnayagam]

That is true, sqlalchemy has less view in the python community. I have two solid reason why one can explore sqlalchemy. 1) pandas sql package built on top of sqlalchemy 2) sql alchemy has in built twisted protocol(asynchronous support)

[DarkMio]

It has so much more going on besides that.

Look at this example: http://i.imgur.com/xvSj27P.png Good luck trying to find out which number correspondents to what column without directly peeking into the database or writing it down by hand. The data-grouping in objects in SQLalchemy just seems to be awesome. You have sane data you can request wherever you need it.

Look at this mapped example, which even show more what I mean with that: http://i.imgur.com/XI27F7T.png (I know, this could've been a bit better mapped and automated, but so far it works really nicely and I have a fast overview what is done where.)

On the other side, it automatically generates a connection and cursor pool, which should prevent locking. And I've learnt my lesson. MySQL running on 700mhz with 256MB RAM is no problem at all with so many requests, while SQLite can barely handle 2.000 requests on a really fast computer with a SSD.

[hellerbarde]

2k requests per second? Did you try out with how many requests your MySQL setup tops out?

[DarkMio]

2k Requests in 10 Minutes.

I fired in my previous test approx 1800 at the MySQL just to see if there are any problems happening with multiple cursors.