<< home

データベースとデータ処理入門

環境構築

作業ディレクトリの作成 mkdir db_practice
ディレクトリ内に入る cd db_practice
仮想環境の作成 python -m venv .venv
仮想環境に入る . .venv/bin/activate
pipアップデート python -m pip install --upgrade pip
必要なパッケージのインストール pip install pandas openpyxl

データベースの作成

作業フォルダの中にファイル作成 touch db_training.py

ファイル名 db_training.py

import pandas as pd
import sqlite3
from datetime import datetime

def create_table_sqlite(sqlite_db,table_name):
    '''
    SQLiteデータベースにテーブルを作成
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: テーブル名
    '''
    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    # テーブル作成
    create_table_query = f'''
    CREATE TABLE IF NOT EXISTS {table_name} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        氏名 TEXT,
        氏名（ひらがな） TEXT,
        年齢 INTEGER,
        生年月日 DATE,
        性別 TEXT,
        血液型 TEXT,
        メールアドレス TEXT,
        電話番号 TEXT,
        携帯電話番号 TEXT,
        郵便番号 TEXT,
        住所 TEXT,
        会社名 TEXT,
        クレジットカード TEXT,
        有効期限 TEXT,
        マイナンバー TEXT
    )
    '''
    cursor.execute(create_table_query)
    conn.commit()

    # 接続を閉じる
    conn.close()

    print(f"Table {table_name} has been successfully created in the {sqlite_db} database.")

if __name__ == '__main__':
    create_table_sqlite('edata.db','dummy_table')

最後のif __name__ == '__main__':は直接起動したときにTrueとなりその下のコードが実行されます。
作業ディレクトリの中でpython db_training.pyで実行

データを使用してレコードを挿入

ファイル名 db_training.py

def insert_record(name, name_hiragana, age, birthdate, gender, blood_type, email, phone, mobile, postal_code,   address, company, credit_card, expiration_date, my_number,sqlite_db,tabel_name):
    # SQLiteデータベースへの接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()
    insert_query = f'''
    INSERT INTO {tabel_name} (氏名, 氏名（ひらがな）, 年齢, 生年月日, 性別, 血液型, メールアドレス, 電話番号, 携帯電話番号, 郵便番号, 住所, 会社名, クレジットカード, 有効期限, マイナンバー)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    '''
    cursor.execute(insert_query, (name, name_hiragana, age, birthdate, gender, blood_type, email, phone, mobile, postal_code, address, company, credit_card, expiration_date, my_number))
    conn.commit()
    print("レコードが正常に挿入されました。")
    # 接続を閉じる
    conn.close()
if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table')#<-コメントアウト
    insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')#<-追加

作業ディレクトリの中でpython db_training.pyで実行

Excelの取り込みとデータベースへの登録

同じファイル db_training.pyに追記します。

def read_excel_to_sqlite(excel_file, sqlite_db, table_name):
    '''
    Excelファイルを読み込んでSQLiteデータベースに書き込む
    :param excel_file: Excelファイルのパス
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: SQLiteデータベースに書き込むテーブル名
    '''
    # Excelファイルを読み込む
    df = pd.read_excel(excel_file)

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)

    df.to_sql(table_name, conn, if_exists='append', index=False)
    print("データが正常に読み込まれ、データベースに挿入されました。")

    # 接続を閉じる
    conn.close()

    print(f"Data has been successfully imported into the {table_name} table of {sqlite_db} database.")

if __name__ == '__main__':の下に下記を追記

if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table') #<-コメントアウト
    # insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')#<-コメントアウト
    read_excel_to_sqlite('Dummy_Data.xlsx', 'edata.db','dummy_table')#<-追加

作業ディレクトリの中でpython db_training.pyで実行

dataの表示

同じファイル db_training.py

def read_sqlite(sqlite_db,table_name):
    '''
    SQLiteデータベースからデータを読み込む
    :param sqlite_db: SQLiteデータベースのパス
    '''

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    # 最初の10レコードを取得するクエリ
    query = f"SELECT * FROM {table_name} LIMIT 10"

    # クエリを実行してデータを取得
    cursor.execute(query)
    rows = cursor.fetchall()

    # データを表示
    for row in rows:
        print(row)

    # 接続を閉じる
    conn.close()
if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table') #<-コメントアウト
    # insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')#<-コメントアウト
    # read_excel_to_sqlite('Dummy_Data.xlsx', 'edata.db','dummy_table')#<-コメントアウト
    read_sqlite('edata.db','dummy_table')#<-追加

作業ディレクトリの中でpython db_training.pyで実行

dataの集計

同じファイル db_training.py

def calculate_age(birthdate):
    # 現在の日付
    now = datetime.now()
    birthdate = datetime.strptime(birthdate, '%Y年%m月%d日')  # 生年月日の形式が'YYYY-MM-DD'の場合
    age = now.year - birthdate.year - ((now.month, now.day) < (birthdate.month, birthdate.day))
    return age
def read_sqlite_to_df(sqlite_db,table_name):
    '''
    SQLiteデータベースからデータを読み込んでデータフレームに変換
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: SQLiteデータベースのテーブル名
    '''

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)

    # データを取得するクエリ
    query = f"SELECT * FROM {table_name}"

    # クエリを実行してデータを取得
    df = pd.read_sql_query(query, conn)

    # 接続を閉じる
    conn.close()

    # 生年月日列の名前（適宜変更してください）
    birthdate_column = '生年月日'
    # 性別列の名前（適宜変更してください）
    gender_column = '性別'

    # 年齢列を追加
    df['age'] = df[birthdate_column].apply(calculate_age)

    # 平均年齢を計算
    average_age = df['age'].mean()

    # 性別ごとの数と割合を計算
    gender_counts = df[gender_column].value_counts()
    gender_ratios = df[gender_column].value_counts(normalize=True)

    # 性別ごとの平均年齢を計算
    average_age_by_gender = df.groupby(gender_column)['age'].mean()

    print("総平均年齢:", average_age)
    print("性別ごとの数:\n", gender_counts)
    print("性別ごとの割合:\n", gender_ratios)
    print("性別ごとの平均年齢:\n", average_age_by_gender)

if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table')#<-コメントアウト
    # insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')#<-コメントアウト
    # read_excel_to_sqlite('Dummy_Data.xlsx', 'edata.db','dummy_table')#<-コメントアウト
    # read_sqlite('edata.db','dummy_table')#<-コメントアウト
    read_sqlite_to_df('edata.db','dummy_table')#<-追加

作業ディレクトリの中でpython db_training.pyで実行

データベースでの検索と表示

同じファイル db_training.py

def search_by_my_number(my_number,sqlite_db,table_name):
    '''
    マイナンバーで検索してデータを表示
    :param my_number: マイナンバー
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: テーブル名
    '''
    # SQLiteデータベースへの接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    search_query = f'''
    SELECT * FROM {table_name} WHERE マイナンバー = ?
    '''
    cursor.execute(search_query, (my_number,))
    result = cursor.fetchone()
    if result:
        # 結果を表示
        print("レコードが見つかりました：")
        print("名前:", result[1])
        print("名前（ひらがな）:", result[2])
        print("年齢:", result[3])
        print("生年月日:", result[4])
        print("性別:", result[5])
        print("血液型:", result[6])
        print("メールアドレス:", result[7])
        print("電話番号:", result[8])
        print("携帯電話番号:", result[9])
        print("郵便番号:", result[10])
        print("住所:", result[11])
        print("会社名:", result[12])
        print("クレジットカード:", result[13])
        print("有効期限:", result[14])
        print("マイナンバー:", result[15])
    else:
        print("該当するレコードが見つかりませんでした。")
    # 接続を閉じる
    conn.close()

if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table')#<-コメントアウト
    # insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')#<-コメントアウト
    # read_excel_to_sqlite('Dummy_Data.xlsx', 'edata.db','dummy_table')#<-コメントアウト
    # read_sqlite('edata.db','dummy_table')#<-コメントアウト
    # read_sqlite_to_df('edata.db','dummy_table')#<-コメントアウト
    search_by_my_number('123456789012','edata.db', 'dummy_table')#<-追加

全体のコード

ファイル名 db_training.py

import pandas as pd
import sqlite3
from datetime import datetime

def create_table_sqlite(sqlite_db,table_name):
    '''
    SQLiteデータベースにテーブルを作成
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: テーブル名
    '''
    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    # テーブル作成
    create_table_query = f'''
    CREATE TABLE IF NOT EXISTS {table_name} (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        氏名 TEXT,
        氏名（ひらがな） TEXT,
        年齢 INTEGER,
        生年月日 DATE,
        性別 TEXT,
        血液型 TEXT,
        メールアドレス TEXT,
        電話番号 TEXT,
        携帯電話番号 TEXT,
        郵便番号 TEXT,
        住所 TEXT,
        会社名 TEXT,
        クレジットカード TEXT,
        有効期限 TEXT,
        マイナンバー TEXT
    )
    '''
    cursor.execute(create_table_query)
    conn.commit()

    # 接続を閉じる
    conn.close()

    print(f"Table {table_name} has been successfully created in the {sqlite_db} database.")

def insert_record(name, name_hiragana, age, birthdate, gender, blood_type, email, phone, mobile, postal_code, address, company, credit_card, expiration_date, my_number,sqlite_db,tabel_name):
    # SQLiteデータベースへの接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()
    insert_query = f'''
    INSERT INTO {tabel_name} (氏名, 氏名（ひらがな）, 年齢, 生年月日, 性別, 血液型, メールアドレス, 電話番号, 携帯電話番号, 郵便番号, 住所, 会社名, クレジットカード, 有効期限, マイナンバー)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    '''
    cursor.execute(insert_query, (name, name_hiragana, age, birthdate, gender, blood_type, email, phone, mobile, postal_code, address, company, credit_card, expiration_date, my_number))
    conn.commit()
    print("レコードが正常に挿入されました。")
    # 接続を閉じる
    conn.close()

def read_excel_to_sqlite(excel_file, sqlite_db, table_name):
    '''
    Excelファイルを読み込んでSQLiteデータベースに書き込む
    :param excel_file: Excelファイルのパス
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: SQLiteデータベースに書き込むテーブル名
    '''
    # Excelファイルを読み込む
    df = pd.read_excel(excel_file)

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)

    df.to_sql(table_name, conn, if_exists='append', index=False)
    print("データが正常に読み込まれ、データベースに挿入されました。")

    # 接続を閉じる
    conn.close()

    print(f"{len(df)} items Data has been successfully imported into the {table_name} table of {sqlite_db} database.")

def read_sqlite(sqlite_db,table_name):
    '''
    SQLiteデータベースからデータを読み込む
    :param sqlite_db: SQLiteデータベースのパス
    '''

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    # 最初の10レコードを取得するクエリ
    query = f"SELECT * FROM {table_name} LIMIT 10"

    # クエリを実行してデータを取得
    cursor.execute(query)
    rows = cursor.fetchall()

    # データを表示
    for row in rows:
        print(row)

    # 接続を閉じる
    conn.close()

def calculate_age(birthdate):
    # 現在の日付
    now = datetime.now()
    birthdate = datetime.strptime(birthdate, '%Y年%m月%d日')  # 生年月日の形式が'YYYY-MM-DD'の場合
    age = now.year - birthdate.year - ((now.month, now.day) < (birthdate.month, birthdate.day))
    return age

def read_sqlite_to_df(sqlite_db,table_name):
    '''
    SQLiteデータベースからデータを読み込んでデータフレームに変換
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: SQLiteデータベースのテーブル名
    '''

    # SQLiteデータベースに接続
    conn = sqlite3.connect(sqlite_db)

    # データを取得するクエリ
    query = f"SELECT * FROM {table_name}"

    # クエリを実行してデータを取得
    df = pd.read_sql_query(query, conn)

    # 接続を閉じる
    conn.close()

    # 生年月日列の名前（適宜変更してください）
    birthdate_column = '生年月日'
    # 性別列の名前（適宜変更してください）
    gender_column = '性別'

    # 年齢列を追加
    df['age'] = df[birthdate_column].apply(calculate_age)

    # 平均年齢を計算
    average_age = df['age'].mean()

    # 性別ごとの数と割合を計算
    gender_counts = df[gender_column].value_counts()
    gender_ratios = df[gender_column].value_counts(normalize=True)

    # 性別ごとの平均年齢を計算
    average_age_by_gender = df.groupby(gender_column)['age'].mean()

    print("総平均年齢:", average_age)
    print("性別ごとの数:\n", gender_counts)
    print("性別ごとの割合:\n", gender_ratios)
    print("性別ごとの平均年齢:\n", average_age_by_gender)

def search_by_my_number(my_number,sqlite_db,table_name):
    '''
    マイナンバーで検索してデータを表示
    :param my_number: マイナンバー
    :param sqlite_db: SQLiteデータベースのパス
    :param table_name: テーブル名
    '''
    # SQLiteデータベースへの接続
    conn = sqlite3.connect(sqlite_db)
    cursor = conn.cursor()

    search_query = f'''
    SELECT * FROM {table_name} WHERE マイナンバー = ?
    '''
    cursor.execute(search_query, (my_number,))
    result = cursor.fetchone()
    if result:
        # 結果を表示
        print("レコードが見つかりました：")
        print("名前:", result[1])
        print("名前（ひらがな）:", result[2])
        print("年齢:", result[3])
        print("生年月日:", result[4])
        print("性別:", result[5])
        print("血液型:", result[6])
        print("メールアドレス:", result[7])
        print("電話番号:", result[8])
        print("携帯電話番号:", result[9])
        print("郵便番号:", result[10])
        print("住所:", result[11])
        print("会社名:", result[12])
        print("クレジットカード:", result[13])
        print("有効期限:", result[14])
        print("マイナンバー:", result[15])
    else:
        print("該当するレコードが見つかりませんでした。")
    # 接続を閉じる
    conn.close()

if __name__ == '__main__':
    # create_table_sqlite('edata.db','dummy_table')
    # insert_record('山田太郎', 'やまだたろう', 30, '1995年02月25日', '男', 'A', 'taro@example.com', '080-1234-5678', '080-8765-4321', '123-4567', '東京都新宿区1-1-1', '株式会社サンプル', '1234-5678-9012-3456', '12/30', '123456789012','edata.db', 'dummy_table')
    # read_excel_to_sqlite('Dummy_Data.xlsx', 'edata.db','dummy_table')
    # read_sqlite('edata.db','dummy_table')
    # read_sqlite_to_df('edata.db','dummy_table')
    search_by_my_number('123456789012','edata.db', 'dummy_table')

if __name__ == '__main__':の下のコードが関数の呼び出しになります。目的のコードの設定の♯を削除すると動作します。

<< home