Chapter3. Sqlite Engine

Any operation has to be done via Sqlite3Engine. The engine is the most top class built on top of Column, Table, Index, MetaData, Insert, Select, Update and Delete.

Connection and Cursor

sqlite3.Connect and sqlite3.Cursor instance are hold as attributes of Sqlite3Engine.

engine = Sqlite3Engine(":memory:")
engine.connect # sqlite3.Connect instance
engine.cursor # sqlite3.Cursor instance

You can call sqlite4dummy.engine.Sqlite3Engine.execute() (some time sqlite4dummy.engine.Sqlite3Engine.executemany()) to execute arbitrary Sql command as you do in generic sqlite3 Python API.

Understand auto commit

Like other relational database system, in sqlite3 you have to perform connect.commit() to make your change such as Insert and Update taken effects.

By default, the autocommit is on. But if you need better performance, you could disable autocommit and manually do commit when you need. For example:

Do this:

engine = Sqlite3Engine("test.db", autocommit=False)

Or:

engine = Sqlite3Engine("test.db")
engine.set_autocommit(False)

Manually do commit:

engine.commit()

Vanilla method

sqlite4dummy minimize some frequently-used commands to let user’s doing complex things in small piece of codes. Basically, it’s just a syntax wrapper. But it’s really helpful.

howmany()

return how many records in a table.

... define a table = Table(...)

count = engine.howmany(table) # SELECT COUNT(*) FROM (SELECT * FROM table)

tabulate()

return all records packed in a list in a table.

data = engine.tabulate(table) # list of record

dictize()

return all records in a column oriented view in a table.

data = engine.dictize(table)
data["#column_name"] # get all column data

to_df()

return all records in pandas.DataFrame view in a table. pandas are required.

df = engine.to_df(table)
df["#column_name"] # get all column data

prt_all()

print all records in a table.

engine.prt_all(table) # this should print all data in a table

remove_all()

remove all data in a table by Table object (or by table name).

engine.remove_all(table)
engine.prt_all(table) # this should print no data.