sqlite3 with Python and in the terminal

using_sqlite3.rst

Common applications of sqlite3 with Python and in the terminal

Documentations

sqlite3: https://www.sqlite.org/cli.html

References

https://stackoverflow.com/questions/305378/list-of-tables-db-schema-dump-etc-using-the-python-sqlite3-api

---

Python

.. ipython:: python

    In [1]: import sqlite3

    In [2]: import pandas as pd

    In [3]: conn = sqlite3.connect("db.sqlite3")

    In [4]: cursor = conn.cursor()

    In [5]: cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    Out[5]: <sqlite3.Cursor at 0x10ea7b810>

    In [6]: tables = cursor.fetchall()

    In [7]: tables
    Out[7]:
    [('django_migrations',),
     ('sqlite_sequence',),
     ('auth_group',),
     ('auth_group_permissions',),
     ('auth_user_groups',),
     ('auth_user_user_permissions',),
     ('django_admin_log',),
     ('django_content_type',),
     ('auth_permission',),
     ('auth_user',),
     ('django_session',)]

    In [8]: query = '''
            SELECT *
            FROM auth_user;
            '''
    In [9]: df = pd.read_sql_query(query, conn)

    In [10]: df
    Out[10]:
       id  password    ...                     date_joined  last_name
    0   1  ********    ...      2018-09-23 23:08:51.539958
    1   2              ...      2018-09-23 23:20:39.727175

Terminal

>>> sqlite3 db.sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.

sqlite> .tables
auth_group                  auth_user_user_permissions
auth_group_permissions      django_admin_log
auth_permission             django_content_type
auth_user                   django_migrations
auth_user_groups            django_session

sqlite> .mode column

sqlite> select * from auth_user;
1   2018-09-23 23:20:55.223899  1             admin                   admin@example.com  1           1
2                               0             nguyen                  mnguyenngo@gmail.  0           1

sqlite> .exit  # to quit sqlite