<jupyter_start><jupyter_text>https://www.pythoncentral.io/introduction-to-sqlite-in-python/Conversion between python and SQL data types:* None type is converted to NULL* int type is converted to INTEGER* float type is converted to REAL* str type is converted to TEXT* bytes type is converted to BLOB<jupyter_code>import sqlite3
db = sqlite3.connect('mydb.db')   # create a .db<jupyter_output><empty_output><jupyter_text>Create a cursor object and pass SQL statements to the cursor object to execute them<jupyter_code>cursor = db.cursor()
sql = """ CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, phone TEXT, email TEXT unique, password TEXT)"""
cursor.execute(sql)
db.commit()<jupyter_output><empty_output><jupyter_text>Insert 2 users into the db<jupyter_code>name1 = 'Andres'
phone1 = '3366858'
email1 = '<EMAIL>'
# A very secure password
password1 = '<PASSWORD>'
 
name2 = 'John'
phone2 = '5557241'
email2 = '<EMAIL>'
password2 = '<PASSWORD>'
 
# Insert user 1
cursor.execute('''INSERT INTO users(name, phone, email, password)
                  VALUES(?,?,?,?)''', (name1,phone1, email1, password1))
print('First user inserted')
# Insert user 2
cursor.execute('''INSERT INTO users(name, phone, email, password)
                  VALUES(?,?,?,?)''', (name2,phone2, email2, password2))
print('Second user inserted')
 
db.commit()<jupyter_output>Second user inserted<jupyter_text>Fetch data<jupyter_code>cursor.execute('''SELECT name, email, phone FROM users''')
user1 = cursor.fetchone() #retrieve the first row
print(user1[0]) #Print the first column retrieved(user's name)
all_rows = cursor.fetchall()
for row in all_rows:
    # row[0] returns the first column in the query (name), row[1] returns email column.
    print('{0} : {1}, {2}'.format(row[0], row[1], row[2]))<jupyter_output>Andres
John : <EMAIL>, 5557241<jupyter_text>Fetch data with WHERE condition<jupyter_code>user_id = 2
cursor.execute('''SELECT name, email, phone FROM users WHERE id=?''', (user_id,))
user = cursor.fetchone()
print(user)
db.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13784705309303458, 'fraction_numerical': 0.026302971261568435, 'mean_word_length': 5.823920265780731, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '450291', 'license': 'MIT', 'n_tokens_mistral': 707, 'n_tokens_neox': 670, 'n_words': 199, 'path': 'SQLite/SQLite tutorial 26-12-2017.ipynb', 'repo_name': 'afcarl/Useful-python', 'size': '2077'}