Ben Chuanlong Du's Blog

It is never too late to learn.

Hands on SQLite3

Tips and Traps

  1. Do NOT use SQLite3 on network filesystems (NFS, SAMBA, etc).

  2. .schema show create statement of a table

  3. You can force query to keep the original order of rows by applying order by rowid.

  4. 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.

  5. 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.

  6. 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.

In [3]:
import sqlite3
import pandas as pd
In [4]:
conn = sqlite3.connect("transactions.sqlite3")
cursor = conn.cursor()
In [5]:
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

In [6]:
query_as_frame("""
    SELECT sqlite_version()
    """, conn)
Out[6]:
sqlite_version()
0 3.37.2

List All Tables

In [8]:
query_as_frame("""
    SELECT * FROM sqlite_master
    """, conn)
Out[8]:
type name tbl_name rootpage sql

Number of Posts

In [7]:
select count(*) from posts
Out[7]:
987

Top 5 Records

In [8]:
select * from posts limit 10
In [11]:
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!!!

  1. 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.

  2. Compare string using like.

  3. 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

In [3]:
select * from posts where category = 'life'
In [5]:
select * from posts where category = 'Life' limit 3
In [6]:
select * from posts where category = 'life' collate nocase limit 3
In [8]:
select * from posts where category like 'life' limit 3
In [10]:
select * from posts where category in ('life') collate nocase
In [12]:
select * from posts where lower(category) in ('life') limit 3
In [3]:
%defaultDatasource jdbc:sqlite:.blogger.sqlite3
In [7]:
select * from sqlite_master
In [8]:
select * from posts limit 10
In [10]:
select rowid, * from posts where title like '%airflow tips%'
In [12]:
select content from posts where rowid = 479
Out[12]:
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

References

In [ ]:

Comments