Hands on SQLite3

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Tips and Traps¶

Do NOT use SQLite3 on network filesystems (NFS, SAMBA, etc).
.schema show create statement of a table
You can force query to keep the original order of rows by applying order by rowid.
SQLite3 supports full-text search by the FTS5 extension (since 3.9.0). It is suggested that you use the porter tokenizer for English searching. Please refer to Section 4.3. Tokenizers of SQLite FTS5 Extension for more details.
Avoid keeping SQLite database file on a NFS filesystem, as the locking mechanism might not work correctly. For details, please refer to https://www.sqlite.org/draft/faq.html#q5.
The window functions are supported since SQLite 3.25.0. Notice that the official Python release 3.6.x does not have SQLite 3.25.0. You have to use official Python release Python 3.7+ if you need SQLite 3.25.0+. However, the Anaconda Python 3.6+ releases include SQLite 3.25.0+.

Please proceed after you have run the comamnd ./main.py r in the directory of blog. This command creates a SQLite3 database named .blogger.sqlite3 and load all articles into a (virtual) table named posts.

import sqlite3
import pandas as pd

conn = sqlite3.connect("transactions.sqlite3")
cursor = conn.cursor()

def query_as_frame(sql, conn):
    cursor.execute(sql)
    columns = [col[0] for col in cursor.description]
    return pd.DataFrame(
        data=cursor.fetchall(),
        columns=columns,
    )

List the Version of SQLite3¶

query_as_frame("""
    SELECT sqlite_version()
    """, conn)

List All Tables¶

query_as_frame("""
    SELECT * FROM sqlite_master
    """, conn)

Number of Posts¶

select count(*) from posts

987

Top 5 Records¶

select * from posts limit 10

select
    *
from
    posts
where
    path like '%poems.markdown'

Last Inserted Row ID of a Table¶

The last inserted rowid of a specific table in SQLite3

String Comparsion¶

By default, string comparison is case-sensitive in SQLite!!!

You can specify collate nocase to make SQLite3 use case-insensitive string comparisons. Unfortunately, this doesn’t work if you use the IN keyword to compare strings.
Compare string using like.
Convert strings to lower/upper case and then compare them.

https://stackoverflow.com/questions/973541/how-to-set-sqlite3-to-be-case-insensitive-when-string-comparing

https://techblog.dorogin.com/case-insensitive-like-in-sqlite-504f594dcdc3

select * from posts where category = 'life'

select * from posts where category = 'Life' limit 3

select * from posts where category = 'life' collate nocase limit 3

select * from posts where category like 'life' limit 3

select * from posts where category in ('life') collate nocase

select * from posts where lower(category) in ('life') limit 3

%defaultDatasource jdbc:sqlite:.blogger.sqlite3

select * from sqlite_master

select * from posts limit 10

select rowid, * from posts where title like '%airflow tips%'

select content from posts where rowid = 479

UUID: a64534c3-c495-436a-a1f6-d21dd3fc135f
Status: published
Date: 2018-10-20 12:32:02
Author: Ben Chuanlong Du
Slug: apache-airflow-tips
Title: Apache Airflow Tips
Category: Programming
Tags: programming

**
Things on this page are
fragmentary and immature notes/thoughts of the author.
It is not meant to readers
but rather for convenient reference of the author and future improvement.
**

https://airflow.apache.org/start.html

https://airflow.apache.org/installation.html


https://airflow.apache.org/tutorial.html

Recursive Common Table Expressions¶

https://www.sqlite.org/lang_with.html

https://stackoverflow.com/questions/34659643/split-a-string-into-rows-using-pure-sqlite

Issues¶

https://stackoverflow.com/questions/25705671/python-attributeerror-module-object-has-no-attribute-connect