Python and SQLite: A beautiful combination

When building small systems which do nice and interesting things, I prefer keeping the implementation as simple as possible. I don't like the philosophy of many people who think too much ahead of time and try to make things work in future requirements too. I like look at problems in a computer scientists perspective than a software engineers perspective. Today I had to build a simple prototype of a system which needs a small back-end database and some simple interactions with it from a front-end. So, without thinking twice, I went for Python as the programming language and SQLite as the database. Simple and easy.

This is a brief note about using Python to interact with SQLite database.

Let's install the sqlite client program first. I'm working on a Ubuntu 16.04 LTS machine.

sudo apt-get install sqlite3 libsqlite3-dev

We can start sqlite3 tool with a name for our database file as follows. The file name is test.db in this case.

sqlite3 test.db

Let's create a simple table and insert some data to it as follows in the prompt.

CREATE TABLE Cars(Id INT, Name TEXT, Price INT);

INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Volvo',12345);

We can query the data we inserted as follows.

SELECT * FROM Cars;

Additionally, we can check the currently created tables and their scheme using following commands.

.tables

.schema

Once we are ready to exit, give the following command on the prompt to exit from the sqlite3 tool.

.exit

Now open a text editor and insert the following python program into it. Save it as sqlite-program.py for the program name.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

#!/usr/bin/python import sqlite3 conn = sqlite3.connect('test.db') print "Opened database successfully"; cursor = conn.execute("SELECT * FROM Cars") for row in cursor: print "id = ", row[0] print "name = ", row[1] print "price = ", row[2], "\n" print "Operation done successfully"; conn.close()

Now we can run this python program to see it taking data from the sqlite database.

python sqlite-program.py

That's all forlks!

References:
[1] http://zetcode.com/db/sqlitepythontutorial/

IP over TCP

In a previous post, I wrote about how to create virtual network interfaces on Linux by using TUN interface facility. We created a simple use program which opened the file descriptors of the TUN interface from the back-end so that any packet directed to the TUN interface will be read by the back-end program through the file descriptor. Similarly, we can write to the file descriptor from the back-end program so that any application listening to the TUN network interface will receive it.

In this article, I'm demonstrating a simple setup based on TUN network interfaces which we can use to deliver our IP packets from one host to another inside a TCP socket connection. This is a simple but an interesting system to demonstrate that we can capture IP packets and then deliver them through any medium we want including TCP sockets. In this particular example, since we are using TCP sockets, we need two programs; a TCP server and a TCP client to run on the two hosts. As IP packets what we are actually sending are ICMP payloads so we should have named this post as ICMP over TCP to be more precise. Let's start the description.

High-level overview of the setup

1. First of all, obtain the required source files from the following git repository.

git clone https://github.com/asanka-code/tun-tcp-socket.git

2. In the first computer, run following commands to setup a TUN interface called asa0,

sudo ip tuntap add dev asa0 mode tun
sudo ip addr add 10.0.1.1/24 dev asa0
sudo ip link set dev asa0 up
sudo ip addr show

3. In the second computer, run following commands to setup a TUN interface called asa0,

sudo ip tuntap add dev asa0 mode tun
sudo ip addr add 10.0.1.2/24 dev asa0
sudo ip link set dev asa0 up
sudo ip addr show

4. Compile and run TUN controller program which is also a TCP server on host 2,

gcc tun-server.c -o tun-server
./tun-server

5. Compile and run TUN controller program which is also a TCP client on host 1,

gcc tun-client.c -o tun-client
./tun-client

6. Ping from host 1 to host 2 where our ping packets will be delivered through the TCP client and TCP server in the TCP socket connection (connection x),

ping -I 10.0.1.1 10.0.1.2

If everything is properly setup. We should be able to see the ping responses from the remote host.