What is the difference between sqlite3 and sqlalchemy?

Question

Beginner question- what is the difference between sqlite and sqlalchemy?

Solution 1

They're apples and oranges.

Sqlite is a database storage engine, which can be better compared with things such as MySQL, PostgreSQL, Oracle, MSSQL, etc. It is used to store and retrieve structured data from files.

SQLAlchemy is a Python library that provides an object relational mapper (ORM). It does what it suggests: it maps your databases (tables, etc.) to Python objects, so that you can more easily and natively interact with them. SQLAlchemy can be used with sqlite, MySQL, PostgreSQL, etc.

So, an ORM provides a set of tools that let you interact with your database models consistently across database engines.

Solution 2

sqlite3 is a embedded RDBMS.

According to this article :

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational database model. A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

SQLAlchemy is a Python ORM.

According to this article :

Object-relational mapping (ORM, O/RM, and O/R mapping) in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language.

Comments

[Martijn]

SQLite can actually be used as 'backend' to SQLAlchemy.

[blueFast]

And it seems that SQLite support is included directed in SQLAlchemy, as opposed to Postgres support, which needs a third-party library (i.e. psycopg2)

[PirateApp]

Upvoted, my question more specifically would be should I use sql alchemy or stick to sqlite, need to bulk read all records every 10 mins, number of records = 2.6 million, number of columns = 6 and insert 100k records every 10 mins in the same table