7t8Z5axa

1# https://stackoverflow.com/q/66004467/2144390
2import pymysql
3import sqlalchemy as sa
4
5print(f"SQLAlchemy version {sa.__version__}")
6print(f"PyMySQL version {pymysql.__version__}")
7
8engine = sa.create_engine("mysql+pymysql://scott:tiger@localhost:3307/tst")
9
10
11def exec_sql(sql, rtn=False):
12    with engine.begin() as conn:
13        rs = conn.execute(sa.text(sql))
14        if rtn:
15            return rs.fetchall()
16
17
18mysql_version = exec_sql("SELECT @@VERSION", True)[0][0]
19print(f"MySQL version {mysql_version}")
20
21exec_sql("""\
22DROP TABLE IF EXISTS tst.intgn_party_test_load
23""")
24exec_sql("""\
25create table tst.intgn_party_test_load (
26  party_id bigint unsigned NOT NULL,
27  party_supertype varchar(15) NOT NULL,
28  carrier_party_id bigint unsigned NOT NULL,
29  full_name varchar(500),
30  lda_actv_ind integer,
31  lda_file_id integer,
32  lda_created_by varchar(100),
33  lda_created_on datetime,
34  lda_updated_by varchar(100),
35  lda_updated_on datetime, 
36  PRIMARY KEY(party_id,party_supertype,carrier_party_id)
37) 
38""")
39exec_sql("""\
40DROP TABLE IF EXISTS tst.raw_listing_20210118175114
41""")
42exec_sql("""\
43create table tst.raw_listing_20210118175114 (
44  id int NOT NULL,
45  full_name varchar(100),
46  ga varchar(100),
47  lda_actv_ind integer,
48  lda_file_id integer,
49  PRIMARY KEY(id)
50) 
51""")
52exec_sql("""\
53INSERT INTO tst.raw_listing_20210118175114
54(id, full_name, ga)
55VALUES
56(1, 'Gord Thompson', 'test001'),
57(2, 'Sir Robert (Bob) Loblaw', 'another test'),
58(3, 'Arnold Rimmer', 'Smeghead')
59""")
60
61exec_sql("""\
62INSERT INTO intgn_party_test_load (
63  party_supertype, 
64  carrier_party_id, 
65  party_id, 
66  full_name, 
67  lda_actv_ind, 
68  lda_file_id) 
69SELECT  
70  'Agency' as s0,
71  0 as s1,
72  CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2,
73  CONCAT(full_name,'-',ga) as s3, 
74  lda_actv_ind, 
75  lda_file_id 
76FROM tst.raw_listing_20210118175114 
77ON DUPLICATE KEY 
78UPDATE  
79  full_name = VALUES(full_name), 
80  lda_actv_ind = VALUES(lda_actv_ind), 
81  lda_file_id = VALUES(lda_file_id) ;
82""")
83
84results = exec_sql("SELECT party_id FROM tst.intgn_party_test_load", True)
85for result in results:
86    result = result[0]
87    print(f"{result} ({len(str(result))} digits)")
88
89"""console output:
90SQLAlchemy version 1.3.23
91PyMySQL version 1.0.2
92MySQL version 5.6.13
93658636859098676345 (18 digits)
946774151756363560397 (19 digits)
9513261150561505492123 (20 digits)
96"""
97