DGKX1ZAt

1from sqlalchemy import *
2from sqlalchemy.exc import InternalError
3
4# [your_mysql_account] / [your_mysql_password] 請換成你自己的帳密
5def connect_sql(database,echo=True):
6    engine = create_engine("mysql+pymysql://[your_mysql_account]:[your_mysql_password]\
7    @localhost:3306/{}".format(database),echo=echo)
8    return engine
9# [your_database_name] 請換成你自己的資料庫名稱
10engine = connect_sql([your_database_name],False)
11
12text_create = '''
13create table SII_REV_{} ( 
14    ID VARCHAR(7) NOT NULL,  
15    NAME VARCHAR(8) NOT NULL, 
16    Sales_This_Month BIGINT(16), 
17    Sales_Last_Month BIGINT(16), 
18    Sales_Last_Year BIGINT(16), 
19    MOM DECIMAL(9,2), 
20    YOY DECIMAL(9,2), 
21    ACC_Sales_This_Year BIGINT(16), 
22    ACC_Sales_Last_Year BIGINT(16), 
23    ACC_YOY DECIMAL(9,2), 
24    Remark VARCHAR(500), 
25    PRIMARY KEY(ID, NAME) 
26);'''
27
28y_m = y_m_generator(2010, 2018)
29y_m_str = [str(y)+'0'+str(m) if m<10 else str(y)+str(m) for y,m in y_m_generator(2010, 2018)]
30
31for p in y_m_str:
32    print(p, end='  ')
33    try:
34        # Drop
35        engine.execute('drop table IF EXISTS SII_REV_{};'.format(p))
36        # Create
37        engine.execute(text_create.format(p))
38    except InternalError:
39        print('table already exists')