<jupyter_start><jupyter_text>1. Database systems are commonly used to manage data in large scale.1. Pandas is great in managing data in a relatively small scale, but it is not good at dealing with transactions (adding, deleting stuff to/from a table) and data managment in a large scale (>a few GB). 1.This course I will demonstate the techniques of relational database (SQL). For an introduction:https://medium.com/@rwilliams_bv/intro-to-databases-for-people-who-dont-know-a-whole-lot-about-them-a64ae9af7121.there are several SQL databases to choose from. The best one is Postgres, MySQL is also very popular but not ideal for data science. These databases require installation of software. For simplicity, we will be using SQLite.<jupyter_code>import sqlite3 # sqlite is a built-in package in python...!
import pandas as pd
import numpy as np
import os
try:
    os.remove('demo.db')
except:
    pass
# create a database called demo
db = sqlite3.connect('demo.db')
asset = pd.read_csv('https://mfin6201.s3.amazonaws.com/company_assets.csv')
asset.to_sql('asset',db,if_exists='replace',index=False)
# Now let's query the database using SQL
pd.read_sql('SELECT permno,fyear,at FROM asset',db)
pd.read_sql('SELECT * FROM asset limit 10',db)
pd.read_sql('SELECT permno,fyear FROM asset WHERE at>10000',db)
# get information for the database
pd.read_sql('''
    SELECT * FROM sqlite_master
''',db)
left = pd.DataFrame({'A': [1,2,3,4],
                        'B': [11,22,33,44]})

right = pd.DataFrame({'A': [1,2,4,5],
                        'C': [11,22,44,55]})
left.to_sql('left_table',db)
right.to_sql('right_table',db)
pd.read_sql('SELECT * FROM left_table',db)
pd.read_sql('''
    SELECT * FROM left_table l
    LEFT JOIN right_table r
    ON l.A = r.A
''',db)
pd.read_sql('''
    SELECT l.A, B, C FROM left_table l
    JOIN right_table r
    ON l.A = r.A
''',db)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12341772151898735, 'fraction_numerical': 0.02531645569620253, 'mean_word_length': 4.168937329700272, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 7, 'https://': 2, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '571774', 'license': 'MIT', 'n_tokens_mistral': 666, 'n_tokens_neox': 620, 'n_words': 205, 'path': 'week2/3.databases.ipynb', 'repo_name': 'leoliu0/FINS5517', 'size': '1896'}