Embedded Databases

MySQL is a Database Server

This is great for security and high performance management.

But sometimes you want to write a standalone program that saves data.

Serialization

The process of encoding software objects/artifacts is usually called serialization. This term refers to the transformation of data into a series of bytes (hence serial) to be stored or transmitted across a network. You may also hear the term marshaling, but that’s a whole other discussion. Naturally, deserialization is the reciprocal process of decoding data that has been stored or delivered.

>>> import json
>>> example = dict()
>>> example['Weninger'] = 38
>>> example['Flynn'] = 42
>>> example['Bui'] = 12
>>> print(example)
{'Weninger': 36, 'Flynn': 42, 'Bui': 12}
>>> with open("data_file.json", "w") as write_file:
...     json.dump(example, write_file)
...

Now if we look to where the data is on disk:

>>> import json
>>> with open("data_file.json", "r") as read_file:
...     data = json.load(read_file)
...
>>> print(data)
{'Weninger': 38, 'Flynn': 42, 'Bui': 12}
>>> data['Weninger']
36

And we could add to it and re-save it if we want.

This is extremely useful:

Used in multi-threading, mutli-processing, Web connections, TONS of things.

But does not have the friendly properties of a relational database.

• What if multiple users were reading and writing simultaneously from the json file?
• What if the write failed?
• What if I inserted a negative value for age?
• What if I wanted to perform a join?

There exist embedded relational databases

SQLite is an embeddable Database. It can be used natively on linux or in C/C++/Python

tweninge@db:~$ sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .exit



Enter SQL statements terminated with a ";"
sqlite> create table faculty_age (name varchar(50) primary key,
   ...> age int not null);
sqlite> .tables
faculty_age
sqlite> select * from faculty_age;
sqlite> insert into faculty_age values ('Weninger', 36);
sqlite> select * from faculty_age;
Weninger|36
sqlite>.exit

>vim test.db

>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> conn.execute("insert into faculty_age values ('McMillan', 39)")
<sqlite3.Cursor object at 0x7f5a1fb90340>

Lets look at the data now… no data is written.

>>> conn.commit()

Now the data is written.

We can search:

>>> cursor = conn.execute("select age from faculty_age");
>>> for row in cursor:
...     age = row[0]
...     print('An age for a professor at Notre Dame is ' + str(age))
...
An age for a professor at Notre Dame is 38
An age for a professor at Notre Dame is 39

But if we do it again:

>>> for row in cursor:
...     age = row[0]
...     print('An age for a professor at Notre Dame is ' + str(age))
...

Nothing prints, why?

Because the cursor is at the end.

Let’s make sure we tidy up and close.

>>> conn.close()
>>> exit()

What to Use and When?

There are some questions regarding when to use SQLite and MySQL. Let’s take a look.

When to Use SQLite?

All applications that require portability and don’t require expansion.

In cases where applications need to read or write files to disk directly.

When to Use MySQL?

Where high-security features are required for data access.

For websites that work on MySQL despite some constraints. It’s a scalable tool that is easy to manage.

Therefore, both SQLite and MySQL are suitable in their own niches with their own functions.