Insert tweet data into a database using SQLite

python_sqlite_demo.py

"""
Python SQLite demo.

The sqlite3 library is a Python builtin. Read more in the Python 3 docs:
    https://docs.python.org/3/library/sqlite3.html

See also the SQLite docs:
    https://www.sqlite.org/docs.html
"""
import sqlite3


conn = sqlite3.connect('db.sqlite')
cur = conn.cursor()


create_sql = """
    CREATE TABLE IF NOT EXISTS tweet(
        id INTEGER PRIMARY KEY,
        status_id INTEGER,
        screen_name VARCHAR(30),
        message VARCHAR(255)
    )
"""
cur.execute(create_sql)
conn.commit()


# Mock data that would be fetched from the API.
# Note each item in the list is a list.
tweets = [
    [123, "foo", "Hello, world!"],
    [124, "bar", "Hello, Tweepy!"],
]

# Note that id is not known upfront but can be left to autoincrement by specifying NULL.
insert_sql = """
    INSERT INTO tweet VALUES (NULL, ?, ?, ?)
"""
cur.executemany(insert_sql, tweets)

fetch_sql = """
    SELECT *
    FROM tweet
"""
cur.execute(fetch_sql)
print(cur.fetchall())

conn.commit()

conn.close()