<jupyter_start><jupyter_text>Sqlite 3<jupyter_code>import sqlite3
import pandas as pd<jupyter_output><empty_output><jupyter_text>Other database software stores data in many different files, but sqlite can store a database in a single file on our machine.  - [SQLite3 Documentation](https://docs.python.org/2/library/sqlite3.html)- [SQL on W3schools](https://www.w3schools.com/sql/)  Create a Database<jupyter_code>conn = sqlite3.connect('data/example.db')
c = conn.cursor()
c.execute('CREATE TABLE person(name text, address text)')
conn.commit()
c.execute("SELECT name FROM sqlite_master WHERE type= 'table'")
c.fetchall()<jupyter_output><empty_output><jupyter_text>Inserting Records into a Database<jupyter_code>c.execute("INSERT INTO person VALUES ('<NAME>', 'Carlow')")
conn.commit()
# Astericks is wildcard and means SELECT all
c.execute("SELECT * 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()<jupyter_output><empty_output><jupyter_text>Primary Keys and Tables All Databases have constraints. You can create your own, but some constraints are default. One of them is the fact that many Databases don't allow you to insert the same data twice. SQLite lets you do this. That's because it assigns Row ID to each new entry. So even though your data looks identical, it has different IDs.<jupyter_code># Add Ann Henry again
c.execute("INSERT INTO person VALUES ('<NAME>', 'Carlow')")
conn.commit()
c.execute("SELECT * FROM person")
c.fetchall()
# Deleting the first Ann Henry row
c.execute('DELETE FROM person where ROWID=1')
conn.commit()
c.execute("SELECT * FROM person")
c.fetchall()
c.execute("INSERT INTO person VALUES ('<NAME>', 'Leitrim')")
conn.commit()
# List row id, name and addresses from DB
c.execute("SELECT ROWID, name, address FROM person")
c.fetchall()<jupyter_output><empty_output><jupyter_text>SQLite3 with Pandas<jupyter_code># Deleting a table. VERY DANGEROUS THING TO DO. 
# Completely deletes the data, and can only be retrieved if you have a backup.
c.execute("DROP TABLE person")
conn.commit()
person = pd.read_csv('data/person.csv')
person.to_sql("person", conn)
c.execute('SELECT * FROM person')
c.fetchall()
cars = pd.read_csv('data/cars.csv')
cars.to_sql("cars", conn)
c.execute('SELECT * FROM cars')
c.fetchall()
county = pd.read_csv('data/county.csv')
county.to_sql("county", conn)
c.execute('SELECT * FROM county')
c.fetchall()
c.execute("SELECT name FROM sqlite_master WHERE type= 'table'")
c.fetchall()
# Delete Duplicate
c.execute("DROP TABLE cars")
conn.commit()
c.execute("SELECT name FROM sqlite_master WHERE type= 'table'")
c.fetchall()<jupyter_output><empty_output><jupyter_text>Other Functionality<jupyter_code>c.execute("""
    SELECT p.NAME, c.Registration
    FROM person as p JOIN car as c ON p.ROWID = c.OwnerId 
""")
c.fetchall()<jupyter_output><empty_output><jupyter_text>Something went wrong with the code above. My data didn't join the same as Ian's. I think it may be because I'd used ROWID whereas Ian used ID. When I used ID, it returned an error, as there's no column with that ID in the table person.<jupyter_code>conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13102820746132848, 'fraction_numerical': 0.003033060357901122, 'mean_word_length': 6.031982942430703, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '564159', 'license': 'Apache-2.0', 'n_tokens_mistral': 1100, 'n_tokens_neox': 1070, 'n_words': 343, 'path': 'Lecture notes and demos/10sqlite3.ipynb', 'repo_name': 'MarionMcG/python-fundamentals', 'size': '3326'}