SQLLite3 - TeamFlowerPower/kb GitHub Wiki

A short sqlite3 database guide

import sqlite3

Build up a database connection, so databases are files in sqlite3:

connection = sqlite3.connect('my.db')

Create a table

tablename = "mytable"
connection.execute("""create table if not exists {tablename}
                          (column1 string, column2 float, column3 int); """.format(tablename=tablename))

Insert some data to the table:

for i in range(1, 100):
    connection.execute("""insert into {tablename}
                              values ('{}', '{}', {});""".format("hello" + str(i), 1 / i, i, tablename=tablename))

There are still more examples of database manipulation possible, e.g. delete values, update and you can do a lot of more complex queries, e.g. natural joins, aggregations, building up indexes, ...

Iterate over all stored values in table tablename:

for row in connection.execute("select * from {tablename};".format(tablename=tablename)):
    print(row)

Aggregate some values:

for row in  connection.execute("select avg(column2), max(column3), count(*) from {tablename};".format(tablename=tablename)):
    print(row)

Store all changed data persistent to database

connection.commit()

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License *.

Code (snippets) are licensed under a MIT License *.

* Unless stated otherwise

⚠️ **GitHub.com Fallback** ⚠️