Kai Hall portfolio

about | code

Python mySql connector practise with sqlalchemy and pymysql.

import mysql.connector
import pymysql as mysql
from sqlalchemy import create_engine
import pandas as pd

login = 'root'
passwd = '_Rootedroot7925'
server = 'localhost'
conn_string = 'mysql+mysqlconnector://{}:{}@{}'.format(login, passwd, server)
engine = create_engine(conn_string, echo=False, encoding='utf-8')
engine.execute('use classicmodels')
engine.execute('show tables').fetchall()

[('customers',),
 ('employees',),
 ('offices',),
 ('orderdetails',),
 ('orders',),
 ('payments',),
 ('productlines',),
 ('products',),
 ('sales',)]

create_table = """
                CREATE TABLE employee_sales(
                    sales_employee VARCHAR(50) NOT NULL,
                    fiscal_year INT NOT NULL,
                    sale DECIMAL(14,2) NOT NULL,
                    PRIMARY KEY(sales_employee,fiscal_year))
                """
engine.execute(create_table)

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x249399740d0>

insert_into = """
                INSERT INTO employee_sales(sales_employee,fiscal_year,sale)
                VALUES
                    ('Bob',2016,100),
                    ('Bob',2017,150),
                    ('Bob',2018,200),
                    ('Alice',2016,150),
                    ('Alice',2017,100),
                    ('Alice',2018,200),
                    ('John',2016,200),
                    ('John',2017,150),
                    ('John',2018,250)
                """
engine.execute(insert_into)

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x24939f176d0>

engine.execute('use classicmodels')
engine.execute('SHOW COLUMNS FROM employee_sales').fetchall()

[('sales_employee', b'varchar(50)', 'NO', 'PRI', None, ''),
 ('fiscal_year', b'int', 'NO', 'PRI', None, ''),
 ('sale', b'decimal(14,2)', 'NO', '', None, '')]

sales_fiscal = """
                SELECT
                    fiscal_year,
                    sales_employee,
                    sale,
                    NTILE (2) OVER (ORDER BY fiscal_year) total_sales
                FROM
                    employee_sales
                """
fiscal_sales = pd.read_sql(sales_fiscal, engine)
fiscal_sales

	fiscal_year	sales_employee	sale	total_sales
0	2016	Alice	150.0	1
1	2016	Bob	100.0	1
2	2016	John	200.0	1
3	2017	Alice	100.0	1
4	2017	Bob	150.0	1
5	2017	John	150.0	2
6	2018	Alice	200.0	2
7	2018	Bob	200.0	2
8	2018	John	250.0	2

# create temp table in first SELECT. select from temp table with second SELECT.

query = """
        WITH productline_sales AS (
        SELECT
            productline,
            year(orderDate) order_year,
            ROUND(SUM(quantityOrdered * priceEach),0) order_value
        FROM orders
        INNER JOIN orderdetails USING (orderNumber)
        INNER JOIN products USING (productCode)
        GROUP BY productline, order_year
        )

        SELECT
            productline,
            order_year,
            order_value,
            NTILE(3) OVER (
                PARTITION BY order_year
                ORDER BY order_value DESC
            ) product_line_group
        FROM
            productline_sales;
        """
n_tile = pd.read_sql(query, engine)
n_tile

	productline	order_year	order_value	product_line_group
0	Classic Cars	2003	1374832.0	1
1	Vintage Cars	2003	619161.0	1
2	Trucks and Buses	2003	376657.0	1
3	Motorcycles	2003	348909.0	2
4	Planes	2003	309784.0	2
5	Ships	2003	222182.0	3
6	Trains	2003	65822.0	3
7	Classic Cars	2004	1763137.0	1
8	Vintage Cars	2004	854552.0	1
9	Motorcycles	2004	527244.0	1
10	Planes	2004	471971.0	2
11	Trucks and Buses	2004	465390.0	2
12	Ships	2004	337326.0	3
13	Trains	2004	96286.0	3
14	Classic Cars	2005	715954.0	1
15	Vintage Cars	2005	323846.0	1
16	Motorcycles	2005	245273.0	1
17	Trucks and Buses	2005	182066.0	2
18	Planes	2005	172882.0	2
19	Ships	2005	104490.0	3
20	Trains	2005	26425.0	3

query = """
        CREATE TABLE productLineSales
        SELECT
            productLine,
            YEAR(orderDate) orderYear,
            quantityOrdered * priceEach orderValue
        FROM
            orderDetails
                INNER JOIN
            orders USING (orderNumber)
                INNER JOIN
            products USING (productCode)
        GROUP BY
            productLine ,
            YEAR(orderDate)
        """
engine.execute(query)

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x2493a0a30d0>

pd.read_sql('SELECT * FROM productLineSales', engine)

	productLine	orderYear	orderValue
0	Vintage Cars	2003	4080.00
1	Classic Cars	2003	5571.80
2	Trucks and Buses	2003	3284.28
3	Trains	2003	2770.95
4	Ships	2003	5072.71
5	Planes	2003	4825.44
6	Motorcycles	2003	2440.50
7	Classic Cars	2004	8124.98
8	Vintage Cars	2004	2819.28
9	Trains	2004	4646.88
10	Ships	2004	4301.15
11	Planes	2004	2857.35
12	Motorcycles	2004	2598.77
13	Trucks and Buses	2004	4615.64
14	Motorcycles	2005	4004.88
15	Classic Cars	2005	5971.35
16	Vintage Cars	2005	5346.50
17	Trucks and Buses	2005	6295.03
18	Trains	2005	1603.20
19	Ships	2005	3774.00
20	Planes	2005	4018.00

#create temp table first.
query = """
        WITH temp AS (
            SELECT
                productLine,
                SUM(orderValue) orderValue
            FROM
                productLineSales
            GROUP BY
                productLine
        )

        SELECT
            productLine,
            orderValue,
            ROUND(
               PERCENT_RANK() OVER (
                  ORDER BY orderValue
               ), 2) percentile_rank
        FROM temp
        """
pd.read_sql(query, engine)

	productLine	orderValue	percentile_rank
0	Trains	9021.03	0.00
1	Motorcycles	9044.15	0.17
2	Planes	11700.79	0.33
3	Vintage Cars	12245.78	0.50
4	Ships	13147.86	0.67
5	Trucks and Buses	14194.95	0.83
6	Classic Cars	19668.13	1.00

query = """
        SELECT
            productLine,
            orderYear,
            orderValue,
            ROUND(
            PERCENT_RANK()
            OVER (
                PARTITION BY orderYear
                ORDER BY orderValue
            ),2) percentile_rank
        FROM
            productLineSales
        """
pd.read_sql(query, engine)

	productLine	orderYear	orderValue	percentile_rank
0	Motorcycles	2003	2440.50	0.00
1	Trains	2003	2770.95	0.17
2	Trucks and Buses	2003	3284.28	0.33
3	Vintage Cars	2003	4080.00	0.50
4	Planes	2003	4825.44	0.67
5	Ships	2003	5072.71	0.83
6	Classic Cars	2003	5571.80	1.00
7	Motorcycles	2004	2598.77	0.00
8	Vintage Cars	2004	2819.28	0.17
9	Planes	2004	2857.35	0.33
10	Ships	2004	4301.15	0.50
11	Trucks and Buses	2004	4615.64	0.67
12	Trains	2004	4646.88	0.83
13	Classic Cars	2004	8124.98	1.00
14	Trains	2005	1603.20	0.00
15	Ships	2005	3774.00	0.17
16	Motorcycles	2005	4004.88	0.33
17	Planes	2005	4018.00	0.50
18	Vintage Cars	2005	5346.50	0.67
19	Classic Cars	2005	5971.35	0.83
20	Trucks and Buses	2005	6295.03	1.00

home