The following post provides code examples in Python inclusive the complete sources for download
- Create a new SQLite database
- Create a new table
- Display of all tables in a database
- Insert values into a table
- View table contents
- Code to download
0 Preliminary two links to SQLite descriptions / tutorials
1 Create a new SQLite database
To create a database it’s enough to make a connect to a non-existing database and the database file will be created automatically.
#!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys def create_new_database(PfadzurDatenbank): try: con = lite.connect(PfadzurDatenbank) con.commit() except lite.Error, e: if con: con.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if con: con.close() create_new_database("/home/pi/sqlite3/BeispielDatenbank.db")
pi@raspberrypi ~/sqlite3 $ ls -al insgesamt 580 drwxr-xr-x 2 pi pi 4096 Okt 21 10:02 . drwxr-xr-x 12 pi pi 4096 Sep 27 20:26 .. -rw-r--r-- 1 pi pi 0 Okt 21 10:02 BeispielDatenbank.db
2 Create a new table
In the following code example, a table will be created, which will be used to store the measured values of a temperature sensor DS18820.
The table consists of three columns
- time stamp
- minimum temperature
- maximum temperature
The goal for this table is to insert timestamps and temperature values. Each
row stands for a timeframe of one hour. The temperatures which are measured within the hour, will be compared to the maximum and minimum values that already stored in the table.
If the new value is greater than the maximum value at the table, it will be replaced. If the new value is less than minimum value in the table, the minimum value will be replaced.
So regardless of the number of measurements (greater than 1) remain three values per hour (timestamp, MinTemp, MaxTemp), which is perfectly adequate for a normal measurement of space or outdoor temperatures.
#!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys def create_new_temperature_sensor_table(PathToDatabase, TableName): try: con = lite.connect(PathToDatabase) cur = con.cursor() cur.executescript(""" CREATE TABLE %s( Timestamp INT PRIMARY KEY, Temp_MIN REAL, Temp_MAX REAL); """ % TableName) con.commit() except lite.Error, e: if con: con.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if con: con.close() create_new_temperature_sensor_table("/home/pi/sqlite3/BeispielDatenbank.db", "Fuehler1")
3 Display of all tables in a database
Currently, only one table (Fuehler1) in the database was created, which can be displayed by using the following script.
#!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys def list_all_tables_in_DB(PathToDatabase): try: con = lite.connect(PathToDatabase) with con: cur = con.cursor() cur.execute("SELECT name FROM sqlite_master WHERE type='table'") rows = cur.fetchall() for row in rows: print row[0] except lite.Error, e: if con: con.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if con: con.close() list_all_tables_in_DB("/home/pi/sqlite3/BeispielDatenbank.db")
pi@raspberrypi ~/python_scripts $ python DB_Funktionen.py Fuehler1
4 Insert values into a table
The following is to be inserted into the table Fuehler1:
- Timestamp: 1445432400
- Temp_MIN: 15.0
- Temp_MAX: 17.22
#!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys def insert_new_line_in_temperature_sensor_table(PathToDatabase, TableName, Timestamp, Temp_MIN, Temp_MAX): try: con = lite.connect(PathToDatabase) cur = con.cursor() cur.execute(""" INSERT INTO %s VALUES( %i , %.3f, %.3f) """ % (TableName, Timestamp, Temp_MIN, Temp_MAX)) con.commit() except lite.Error, e: if con: con.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if con: con.close() insert_new_line_in_temperature_sensor_table("/home/pi/sqlite3/BeispielDatenbank.db", "Fuehler1", 1445432400, 15.0, 17.22)
5 View table contents
#!/usr/bin/python # -*- coding: utf-8 -*- import sqlite3 as lite import sys def show_all_table_entries(PathToDatabase, TableName): try: con = lite.connect(PathToDatabase) cur = con.cursor() cur.execute("SELECT * FROM %s" % TableName) lines = cur.fetchall() for line in lines: print line con.commit() except lite.Error, e: if con: con.rollback() print "Error %s:" % e.args[0] sys.exit(1) finally: if con: con.close() show_all_table_entries("/home/pi/sqlite3/BeispielDatenbank.db", "Fuehler1")
pi@raspberrypi ~/python_scripts $ python DB_Funktionen.py (1445432400, 15.0, 17.22)
6 Code to download
download: DB_Funktionen.py