What's the difference between a database and a DBMS?

The useful distinction is that a database is an organized collection of data, while a database management system, or DBMS, is software that manages access to that data. The DBMS provides ways to retrieve and change it, and may handle things like transactions and concurrent access.

In everyday conversation, people often say database when they mean the whole setup. That explains a lot of the apparent contradiction in definitions.

A CSV is a file format. It can hold organized records, but the file itself doesn't provide a query language, locking, transactions, or other management features. Code that reads and updates it supplies those behaviors.

Some people would call the organized CSV data a simple database. Others reserve that word for something with more access facilities and call the CSV a data file instead. Either way, a CSV backed system can be built, but the guarantees come from the software you write, not from CSV itself.

SQLite is a DBMS. In Python, the sqlite3 module lets your program use SQLite to work with a database, which is commonly stored in a file. SQL is the language you use to ask for data or make changes, not the storage format itself.

So the file holds the data, SQLite manages it, and your Python code calls on SQLite. Calling the file a SQLite database is normal, even though the file isn't doing the management by itself.

Don't make ACID the definition of a database. Those letters describe transaction guarantees: atomicity, consistency, isolation, and durability. Some database systems provide them, while others make different tradeoffs. SQL and the relational model aren't requirements for every database either.