GeoEco.Datasets.SQLite
A Table for accessing SQLite tables accessible through Python’s built in sqlite3 module.
This example shows how to create an in-memory database, create a table within it, and add some fields:
from GeoEco.Datasets.SQLite import SQLiteDatabase
db = SQLiteDatabase(':memory:')
table = db.CreateTable('TempTable1')
table.AddField('FloatField', 'float64')
table.AddField('IntField', 'int32')
table.AddField('StrField', 'string', isNullable=True)
table.AddField('DateTimeField', 'datetime')
for field in table.Fields:
print(field.Name, field.DataType, field.IsNullable)
Output:
ObjectID oid False
FloatField float64 False
IntField int32 False
StrField string True
DateTimeField datetime False
Continuing from above, let’s insert some data with
SQLiteTable.OpenInsertCursor():
from datetime import datetime
with table.OpenInsertCursor() as cursor:
cursor.SetValue('FloatField', 1.1)
cursor.SetValue('IntField', 1)
cursor.SetValue('StrField', 'abc')
cursor.SetValue('DateTimeField', datetime(2000, 1, 2, 3, 4, 5))
cursor.InsertRow()
cursor.SetValue('FloatField', 2.2)
cursor.SetValue('IntField', 2)
cursor.SetValue('StrField', 'def')
cursor.SetValue('DateTimeField', datetime(2000, 6, 7, 8, 9, 10))
cursor.InsertRow()
cursor.SetValue('FloatField', 3.3)
cursor.SetValue('IntField', 3)
cursor.SetValue('StrField', None)
cursor.SetValue('DateTimeField', datetime(2000, 11, 12, 13, 14, 15))
cursor.InsertRow()
And read it back using SQLiteTable.OpenSelectCursor():
with table.OpenSelectCursor() as cursor:
while cursor.NextRow():
print(', '.join([f'{field.Name} = {cursor.GetValue(field.Name)}' for field in table.Fields]))
Output:
ObjectID = 1, FloatField = 1.1, IntField = 1, StrField = abc, DateTimeField = 2000-01-02 03:04:05
ObjectID = 2, FloatField = 2.2, IntField = 2, StrField = def, DateTimeField = 2000-06-07 08:09:10
ObjectID = 3, FloatField = 3.3, IntField = 3, StrField = None, DateTimeField = 2000-11-12 13:14:15
Read it back using SQLiteTable.Query(), which returns a dict:
for field, values in table.Query().items():
print(f'{field}: {values!r}')
Output:
ObjectID: [1, 2, 3]
FloatField: [1.1, 2.2, 3.3]
IntField: [1, 2, 3]
StrField: ['abc', 'def', None]
DateTimeField: [datetime.datetime(2000, 1, 2, 3, 4, 5), datetime.datetime(2000, 6, 7, 8, 9, 10), datetime.datetime(2000, 11, 12, 13, 14, 15)]
Read it back into a pandas.DataFrame:
import pandas as pd
df = pd.DataFrame(table.Query())
print(df)
print('')
Output:
ObjectID FloatField IntField StrField DateTimeField
0 1 1.1 1 abc 2000-01-02 03:04:05
1 2 2.2 2 def 2000-06-07 08:09:10
2 3 3.3 3 None 2000-11-12 13:14:15
Let’s use SQLiteTable.OpenUpdateCursor() to delete the second row and
update the values of the third row. OpenUpdateCursor(),
OpenSelectCursor(), and Query(), all
support “where” and “order by” expressions to filter and order the rows. We’ll
use “where” here to skip the first row.
with table.OpenUpdateCursor(where='ObjectID >= 2') as cursor:
cursor.NextRow()
cursor.DeleteRow()
cursor.NextRow()
cursor.SetValue('FloatField', 3.333)
cursor.SetValue('StrField', 'ghi')
cursor.UpdateRow()
print(pd.DataFrame(table.Query()))
Output:
ObjectID FloatField IntField StrField DateTimeField
0 1 1.100 1 abc 2000-01-02 03:04:05
1 3 3.333 3 ghi 2000-11-12 13:14:15
Classes
A |
|
A |