Scribd
Upload
116 views3 pages

SQLite Database Operations Guide

The document shows code for interacting with an SQLite database called "davidStuff.db" from a Jupyter notebook. It creates a table called "datetime_int" with a column for storing date/time values as integers, inserts the current time in different formats, and queries the table to return the inserted values. Various SQL commands are executed via Python functions to create, insert, and select data from the single-table database.

Uploaded by

David Watson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
116 views3 pages

SQLite Database Operations Guide

The document shows code for interacting with an SQLite database called "davidStuff.db" from a Jupyter notebook. It creates a table called "datetime_int" with a column for storing date/time values as integers, inserts the current time in different formats, and queries the table to return the inserted values. Various SQL commands are executed via Python functions to create, insert, and select data from the single-table database.

Uploaded by

David Watson
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

3/19/2019 SQLite

In [2]: import sqlite3


import [Link] as plt
import pandas as pd

%matplotlib inline

db = '[Link]'
def run_query(q):
with [Link](db) as conn:
return pd.read_sql(q, conn)
def run_command(c):
with [Link](db) as conn:
conn.isolation_level = None
[Link](c)
[Link]('PRAGMA foreign_keys = ON;')

def show_tables():
q='''
SELECT name,type
FROM sqlite_master
WHERE type IN ("table","view");
'''
return run_query(q)

In [ ]:

In [7]: c = '''
CREATE TABLE
IF NOT EXISTS datetime_int (
d1 int
);
'''
run_command(c)
print(show_tables())

name type
0 datetime_int table

In [8]: c='''
INSERT INTO datetime_int (d1)
VALUES
(strftime('%s','now'));
'''
run_command(c)

[Link] 1/3
3/19/2019 SQLite

In [9]: c= '''
SELECT
d1
FROM
datetime_int;
'''
print(run_query(c))

d1
0 1546318845

In [10]: c='''
SELECT
datetime(d1,'unixepoch')
FROM
datetime_int;
'''
print(run_query(c))

datetime(d1,'unixepoch')
0 2019-01-01 [Link]

In [16]: c='''
INSERT INTO datetime_int (d1)
VALUES
(
datetime('now', 'localtime')
);
'''
run_command(c)

In [23]: c='''
SELECT
datetime(d1,'unixepoch')
FROM
datetime_int;
'''
print(run_query(c))

datetime(d1,'unixepoch')
0 2019-01-01 [Link]
1 None
2 None
3 None

[Link] 2/3
3/19/2019 SQLite

In [18]: c='''
SELECT
d1
FROM
datetime_int;
'''
print(run_query(c))

d1
0 1546318845
1 2018-12-31 [Link]

In [21]: c='''
INSERT INTO datetime_int (d1)
VALUES
(
datetime('now')
);
'''
run_command(c)

In [22]: c='''
SELECT
d1
FROM
datetime_int;
'''
print(run_query(c))

d1
0 1546318845
1 2018-12-31 [Link]
2 None
3 2019-01-01 [Link]

In [ ]:

[Link] 3/3

You might also like