RemoteSqlite
RemoteSqlite is a way to easily access a remote Sqlite database and perform queries, including SELECT and INSERT operations
The remote database is referenced through a PyFilesystem2 url, such as:
osfs://path/to/files3://path/to/file
You can perform pull and push operations that transfer the database
to a local temp directory
See below for examples of how to use
InĀ [1]:
from remote_sqlite import RemoteSqlite
db = RemoteSqlite('osfs:///Users/vbalasubramaniam/Downloads/Northwind_large.sqlite', always_download=True)
db.get_counts()
Out[1]:
[{'Employee': 9},
{'Category': 8},
{'Customer': 91},
{'Shipper': 3},
{'Supplier': 29},
{'Order': 16818},
{'Product': 77},
{'OrderDetail': 621883},
{'CustomerCustomerDemo': 0},
{'CustomerDemographic': 0},
{'Region': 4},
{'Territory': 53},
{'EmployeeTerritory': 49},
{'posts': 0},
{'posts_data': 2},
{'posts_idx': 0},
{'posts_content': 0},
{'posts_docsize': 0},
{'posts_config': 1},
{'order_search': 16818},
{'order_search_data': 226},
{'order_search_idx': 474},
{'order_search_content': 16818},
{'order_search_docsize': 16818},
{'order_search_config': 1}]
InĀ [2]:
db.select("""SELECT * FROM Shipper""")
Out[2]:
[{'Id': 1, 'CompanyName': 'Speedy Express', 'Phone': '(503) 555-9831'},
{'Id': 2, 'CompanyName': 'United Package', 'Phone': '(503) 555-3199'},
{'Id': 3, 'CompanyName': 'Federal Shipping', 'Phone': '(503) 555-9931'}]
InĀ [3]:
new_records = [{'Id': 4, 'CompanyName': 'Speedy Express', 'Phone': '(503) 555-9831'},
{'Id': 5, 'CompanyName': 'United Package', 'Phone': '(503) 555-3199'},
{'Id': 6, 'CompanyName': 'Federal Shipping', 'Phone': '(503) 555-9931'}]
InĀ [4]:
db.insert('Shipper', new_records)
InĀ [5]:
db.con.execute('DELETE FROM Shipper WHERE Id >3')
Out[5]:
<sqlite3.Cursor at 0x10535bab0>
InĀ [6]:
create_statement = """CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
t TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)"""
InĀ [7]:
db.con.execute(create_statement)
Out[7]:
<sqlite3.Cursor at 0x1053a43b0>
InĀ [8]:
db.insert('test', [{'name': 'John'},{'name':'Matt'}])
InĀ [9]:
db.select('SELECT * FROM test')
Out[9]:
[{'id': 1, 'name': 'John', 't': datetime.datetime(2020, 3, 7, 22, 12, 45)},
{'id': 2, 'name': 'Matt', 't': datetime.datetime(2020, 3, 7, 22, 12, 45)}]
InĀ [10]:
db.con.execute('DROP TABLE test')
Out[10]:
<sqlite3.Cursor at 0x1053a42d0>
InĀ [11]:
db.generate_create_table('Shipper2', [{'Id': 1, 'CompanyName': 'Speedy Express', 'Phone': '(503) 555-9831'},
{'Id': 2, 'CompanyName': 'United Package', 'Phone': '(503) 555-3199'},
{'Id': 3, 'CompanyName': 'Federal Shipping', 'Phone': '(503) 555-9931'}])
Out[11]:
'CREATE TABLE "Shipper2" ("Id" TEXT, "CompanyName" TEXT, "Phone" TEXT)'
InĀ [12]:
db.con.execute('CREATE TABLE "Shipper2" ("Id" TEXT, "CompanyName" TEXT, "Phone" TEXT)')
Out[12]:
<sqlite3.Cursor at 0x1053a4340>
InĀ [13]:
db.insert('Shipper2', [{'Id': 1, 'CompanyName': 'Speedy Express', 'Phone': '(503) 555-9831'},
{'Id': 2, 'CompanyName': 'United Package', 'Phone': '(503) 555-3199'},
{'Id': 3, 'CompanyName': 'Federal Shipping', 'Phone': '(503) 555-9931'}])
InĀ [14]:
db.select('SELECT * FROM Shipper2')
Out[14]:
[{'Id': '1', 'CompanyName': 'Speedy Express', 'Phone': '(503) 555-9831'},
{'Id': '2', 'CompanyName': 'United Package', 'Phone': '(503) 555-3199'},
{'Id': '3', 'CompanyName': 'Federal Shipping', 'Phone': '(503) 555-9931'}]
InĀ [Ā ]:
