Tom Boothby's sqlite-tutorial

How to create a simple database in sqlite using SAGE.

In SAGE, type

sage: import sqlite3

To create a filename for the database

sage: conn = sqlite3.connect("/full/path/mydata.db")

This is a blank database at the moment. Now create a table with fields labeled/typed as follows:

sage: connection.execute("CREATE TABLE elliptic_curves (cremona_label text primary key, a_1 int, a_2 int, a_3 int, a_4 int, a_6 int, rank int, regulator float)")

This creates a sqlite3.cursor object. Now let's enter an entry into this database

sage: E = EllipticCurve("389a")
sage: connection.execute("INSERT INTO elliptic_curves(cremona_label,a_1,a_2,a_3,a_4,a_6,rank,regulator) VALUES (?,?,?,?,?,?,?,?)", ('389a', int(E.a1()), int(E.a2()), int(E.a3()), int(E.a4()), int(E.a6()), int(E.rank()), float(E.regulator())))

This is our new entry in the database mydata! (If this fails with a mysterious error then possibly the entries, such as E.a1(), need to be coerced into the correct data type, such as int(E.a1()).)

Queries

To "query" all the elliptic curves of rank 2, type:

sage: result = connection.execute("SELECT * FROM elliptic_curves WHERE rank=1")
sage: print result.fetchone()
None
sage: result = connection.execute("SELECT * FROM elliptic_curves WHERE rank=2")
sage: print result.fetchone()
(u'389a', 0, 0, 1, 1, -2, 0, 0, 2, 0.15246017794314401)
sage: print result.fetchone()
None

will return the first one (replacing "fetchone" by "fetchall" will return all).

Deleting an entry

Suppose you entered something wrong and you want to delete an entry. You use the "primary key" to delete an entry:

sage: connection.execute("DELETE FROM elliptic_curves WHERE cremona_label = ?",("389a",))