# 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",))