SQL Database in Python

sqlite3

import sqlite3
connection = sqlite3.connect("5 - customersales.db")
cur = connection.cursor()
resultest = cur.execute("select * from tbl_sales;")

for item in resultest:
    print(item)

    <sqlite3.Connection object at 0x0000027D2C1A56C0>
    (1, '12/31/2014', 'Purchased from Store', 2, 2, 20, 1)
    (2, '1/15/2015', 'Phone Purchase', 1, 1, 30, 2)
    (3, '6/14/2015', 'Internet Purchase', 3, 3, 5, 1)
    (4, '11/11/2015', 'Sales Convention Purchase', 3, 3, 500, 100)
    (5, '4/18/2016', 'Internet Purchase', 4, 1, 20, 2)
    (6, '10/15/2016', 'Purchased from Store', 5, 1, 20, 1)
    (7, '3/17/2017', 'Internet Purchase', 4, 1, 20, 1)
    (8, '6/15/2018', 'Purchased from Store', 3, 3, 5, 1)
    (9, '5/25/2019', 'Internet Purchase', 1, 3, 10, 2)
    (10, '6/9/2019', 'Internet Purchase', 2, 3, 10, 2)

DB 접근

# Query 를 실행 한 후 바로 판다스로 가져오는 방식을 사용한다.
import pandas as pd
df_sales1 = pd.read_sql_query("select * from tbl_sales;", connection, index_col = "Sale_ID")
df_sales1["Sale_Date"] = pd.to_datetime(df_sales1["Sale_Date"])
df_sales1.sort_values(by = "Sale_Date")

# import sqlalchemy
result_data = df_sales1[(df_sales1["Sales_Amount"] > 10) & (df_sales1["Sales_Quantity"] == 2)& (df_sales1["Customer_ID"] == 4)]

result_data.to_sql("mytest", con = connection)
mydata = pd.read_sql("select * from mytest;", con=connection)
mydata