# 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'] = 36
>>> 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:
...
>>> print(data)
{'Weninger': 36, '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', 37)")
<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 36
An age for a professor at Notre Dame is 37


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.