I am supposed to give an introductory course to SQL and databases next semester. The students are supposed to know basics in Python programming already (we are using Python 3.8 or later) and had some exposition to networking. I like the students to use local DBs that they create on their devices as well as sometimes work with a remote DB on a server. The computer room comes with Windows PCs, but students can in principle bring their own devices, and it would therefore be nice if Apple and Linux would be supported without to much hassle. It would also be nice if the setup would be mostly open source (no fees and stuff).

What combination of DBMS (preferably the same for the local devices as well as the remote DB server) and Python library would you recommend for this, for what reasons and where could I find good resources for the suggested combo?

Here’s what I dug up on the web so far. Some stuff seems to be 15 years old (and relying on Python 2). Also there doesn’t seem to be clear cut statements of the sorts “Compared over all possibilities this would be recommended for beginners”, hence some expert’s opinion is warmly welcome.

• DBMS: sqlite, Library: sqlite 3 (ref: RealPython, python-course.eu)

  Pros: Does not require additional installations. Light weight.

  Cons: It seems that multiuser access to a remote server would not be easy to get safely working.

• DBMS: mysql, Library: mysql.connector (ref: RealPython, W3schools, MySQLTutorial)

  Pros: Seems to fulfill all requirements

  Cons: Needs installation of mysql-connector-python through pip. Students need to setup mysql DB separately on their own device. Students need to start mysql DB seperately on their devices each time.

• DBMS: mysql, Library: pymysql (ref: stackoverflow0, freeCodeCamp)

  Pros/Cons: ???

• DBMS: mysql, Library: MySQLdb (ref: stackoverflow1, python-course.eu)

  Cons: Essentially as last one and only works for Python 2 – correct me if I am wrong.

• DBMS: mysql, other Libraries: SQLAlchemy, peewee, Storm, Flask, Django, … (ref: stackoverflow1, stackoverflow2 stackoverflow3)

  Pros: ???

  Cons: Are they currently usable or outdated like MySQLdb?

• DBMS: MariaDB, Library: mariadb (ref: mariadb)

  Pros: Slightly better in open source issues than MySQL.

  Cons: ??? How does this compare to MySQL combos?

• DBMS: MariaDB, Library: Other MySQL libraries

  Pros: Slightly better in open source issues than MySQL.

  Cons: Which Libraries for MySQL work for MariaDB? Do they need adjustments? Are there separate ones?

• DBMS: PostgreSQL, Library: psycopg2 (ref: RealPython)

  Pros/Cons: Seems comparable to MySQL on first glance.

Thank you for any helpful opinions.