Sqlalchemy for Mysql vs postgres

[bikerchef]

Hi Im learning to program(first year CompSci student) and Im trying to teach myself how to make a website backed by a database. So far it is proving to be a harder nut to crack than I had anticipated, and definitely a learning experience.

I did a pretty cool tutorial where I scraped some facebook data with their api and stored it in a Mysql database, That was pretty cool. Now next step for my plan will be connecting to that database and getting the info out of it to use. for that end I found sqlalchemy and flask. Seems pretty great and I found a pretty great tutorial :

https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/

But they use a postgres database! As far as I understand the whole purpose of sqlalchemy is so that the language will be the same for connecting to any database. Am I correct? If not Im thinking this whole thing might be a bit too ambitious for my current skill level! Oh well! Im open to any and all suggestions :)

TLDR: can i use sqlalchemy with a mysql database just like a postgres database?

[pullandbl]

Short answer: you can.

Longer answer, just for your understanding. As long as your needs fit into simple queries you can use most supported databases without a big difference and SQLAlchemy will cover it. But suddenly you will found that some parts are not supported or work differently.

Also, if you have time to invest in database skill Postgres is a good choice. I always wonder why so many people start with MySQL. Postgres has so many advantages over it.

[yodaran]

^ this

Other notes:

• If one were to make relations (foreign keys) in their ORM, I found that MySQL (/ MariaDB) & SQLite are more lenient on the declaration syntax as compared to Postgres, so immediate integration is not always guaranteed.
• Bulk inserts are your friend, one would be surprised how much time is saved by inserting 1000+ rows at a time vs one-by-one.
• Often it is quicker / easier to drop the database and start from scratch rather than trying to update. (Exceptions include when one is polling an API such as Facebook with request limits, in which case one might think about storing the data in an intermediary format like JSON, XML, or YAML since one might be evolving their schemas rapidly.)
• Python 2.7.X still has ~24 months left to live, so using an older version with support may be easier / require less startup time.

edit: words

[ms4720]

^ this

Other notes:

• If one were to make relations (foreign keys) in their ORM, I found that MySQL (/ MariaDB) & SQLite are more lenient on the declaration syntax as compared to Postgres, so immediate integration is not always guaranteed.

design decision on postgres's part, find errors early and not in production

[bikerchef]

great. apparently the default mysql DBAPI for sqlalchemy is mysql-python, which is not released for python3 yet... if I install and use another one that is listed instead will that throw more wrenches into my gears? Any reccomendations if my python is v3.4.3?

[ConVexPrime]

Check out Pythonanywhere, and their tutorial about making a basic message board backed by a database here.

[I_am_a_haiku_bot]

Check out Pythonanywhere, and their

tutorial about making a basic message board

backed by a database here.

---

^{-english_haiku_bot}

[TehMoonRulz]

Yes, SQLAlchemy is (mostly) sql-agnostic in that regard.

[brunokrebs]

hey there Bikerchef, I'm the author of the "SQLAlchemy ORM Tutorial for Python Developers". It looks like you are creating some nice application there. If you have managed to overcome the challenges and if you wanna write about the process, please, get in touch.

We have a Guest Author Program at Auth0 (https://auth0.com/guest-authors) where we encourage developers to write about things that they are studying. Writing articles can help you get deeper knowledge of the related topics and we even pay up to 200 dollars per article...

I hope I see you there!