<jupyter_start><jupyter_text>Creating a database with sqlalchemy and sqlite Quick demonstration of how to create a database to use to store datasets that are too big to store in memory.   I created a database and populated it from a 2.5gb csv file, so that I could run SQL queries to get small datasets into memory rather than the whole thing. (Dataset and database not included in this repository as they are too big, you can find the raw dataset [here on kaggle](https://www.kaggle.com/yingwurenjian/chicago-divvy-bicycle-sharing-data/)).    This notebook has had its output cells frozen so you can still see the results of running the code, and that the finished database did actually work.<jupyter_code>import pandas as pd

import warnings
warnings.filterwarnings("ignore")

import sqlite3
from sqlalchemy import create_engine
# Setting variables for data directory and file.
DIR = "./data"
FILE = '/data.csv'

file = '{}{}'.format(DIR, FILE)

# Quick test we have the correct path.
print('File directory: {}'.format(file))
# Test data import
df = pd.read_csv(file, nrows=2)
df.head()
# Connecting to the database
csv_database = create_engine('sqlite:///csv_database.db')
# Building the database via chunking
chunksize = 1000000
i = 0

for df in pd.read_csv(file, chunksize = chunksize, iterator=True):
    df = df.rename(columns = {c: c.replace(' ', '') for c in df.columns})
    df.index += i
    
    df.to_sql('data', csv_database, if_exists = 'append')
    i = df.index[-1]+1
    
    print('|  index: {}'.format(i))
df = pd.read_sql_query('SELECT * FROM data WHERE from_station_id=131', csv_database)
df.tail()
df.count()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10234798314268513, 'fraction_numerical': 0.010234798314268514, 'mean_word_length': 4.431372549019608, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '639017', 'license': 'MIT', 'n_tokens_mistral': 520, 'n_tokens_neox': 499, 'n_words': 200, 'path': 'sqlalchemy & sqlite workbooks - Creating the database.ipynb', 'repo_name': 'DanielPDWalker/sqlachemy-sqlite-workbook', 'size': '1661'}