<jupyter_start><jupyter_text>SQLite in Python<jupyter_code>import sqlite3
# Establish connection to db.
conn = sqlite3.connect('data/example.db')
# Create a Cursor object and call its execute() method to perform SQL commands
c = conn.cursor()
# Create table. id INTEGER PRIMARY KEY - to create unique id
c.execute('''CREATE TABLE person(name text, address text)''')
conn.commit()
# Show table.
c.execute('''SELECT name FROM sqlite_master WHERE type ='table' ''')
c.fetchall()
# Insert a row of data.
c.execute("INSERT INTO person VALUES ('<NAME>','Carlow')")
conn.commit()
c.execute('SELECT name, address from person')
c.fetchall()
person = [('<NAME>', 'Tipperary'), ('<NAME>', 'Kerry'), ('<NAME>', 'Monaghan')]
person
c.executemany('INSERT INTO person VALUES (?,?)', person)
conn.commit()
c.execute('SELECT * FROM person')
c.fetchall()
c.execute('SELECT ROWID, name, address FROM person')
c.fetchall()
# Insert a row of data.
c.execute("INSERT INTO person VALUES ('<NAME>','Carlow')")
conn.commit()
c.execute('SELECT ROWID, name, address FROM person')
c.fetchall()
c.execute('DELETE FROM person WHERE ROWID=1')
conn.commit()
# Delete a row of data.
c.execute('SELECT ROWID, name, address FROM person')
c.fetchall()
# Insert a row of data.
c.execute("INSERT INTO person VALUES ('<NAME>','Leitrim')")
conn.commit()
c.execute('SELECT ROWID, name, address FROM person')
c.fetchall()<jupyter_output><empty_output><jupyter_text>SQLite3 with pandas<jupyter_code>import pandas as pd
c.execute('DROP TABLE person')
conn.commit()
person = pd.read_csv('https://raw.githubusercontent.com/ianmcloughlin/datasets/master/cars-db/person.csv', index_col=0)
person.head(10)

person.to_sql('person', conn)
car = pd.read_csv('https://raw.githubusercontent.com/ianmcloughlin/datasets/master/cars-db/car.csv', index_col=0)
car.to_sql('car', conn)
c.execute('SELECT * FROM car')
c.fetchall()
county = pd.read_csv('https://raw.githubusercontent.com/ianmcloughlin/datasets/master/cars-db/county.csv', index_col=0)
county.to_sql('county', conn)
c.execute('SELECT * FROM county')
c.fetchall()
c.execute('''SELECT name FROM sqlite_master WHERE type ='table' ''')
c.fetchall()
c.execute('''
    SELECT p.Name, c.Registration, p.Address
    FROM person as p JOIN car as c ON p.ID = c.OwnerId
''')
c.fetchall()
c.execute("""
    SELECT p.Name, c.Registration, p.Address
    FROM person as p
         JOIN car as c ON p.ID = c.OwnerId
         JOIN county as t ON t.Name = p.Address
    WHERE c.Registration NOT LIKE '%-' + t.Registration + '-%'
""")
c.fetchall()
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16388349514563108, 'fraction_numerical': 0.003495145631067961, 'mean_word_length': 5.252427184466019, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '128419', 'license': 'MIT', 'n_tokens_mistral': 902, 'n_tokens_neox': 882, 'n_words': 236, 'path': 'SQLite.ipynb', 'repo_name': 'mizydorek/Random', 'size': '2604'}