2jzXJrNG

1# https://stackoverflow.com/q/66004467/2144390
2import MySQLdb
3import sqlalchemy as sa
4
5print(sa.__version__)  # 1.3.22
6print(MySQLdb.version_info)  # (2, 0, 3, 'final', 0)
7
8engine = sa.create_engine("mysql+mysqldb://scott:tiger@localhost:3307/tst")
9
10
11def exec_sql(sql):
12    with engine.begin() as conn:
13        conn.execute(sa.text(sql))
14
15
16exec_sql("""\
17DROP TABLE IF EXISTS tst.intgn_party_test_load
18""")
19exec_sql("""\
20create table tst.intgn_party_test_load (
21  party_id bigint unsigned NOT NULL,
22  party_supertype varchar(15) NOT NULL,
23  carrier_party_id bigint unsigned NOT NULL,
24  full_name varchar(500),
25  lda_actv_ind integer,
26  lda_file_id integer,
27  lda_created_by varchar(100),
28  lda_created_on datetime,
29  lda_updated_by varchar(100),
30  lda_updated_on datetime, 
31  PRIMARY KEY(party_id,party_supertype,carrier_party_id)
32) 
33""")
34exec_sql("""\
35DROP TABLE IF EXISTS tst.raw_listing_20210118175114
36""")
37exec_sql("""\
38create table tst.raw_listing_20210118175114 (
39  id int NOT NULL,
40  full_name varchar(100),
41  ga varchar(100),
42  lda_actv_ind integer,
43  lda_file_id integer,
44  PRIMARY KEY(id)
45) 
46""")
47exec_sql("""\
48INSERT INTO tst.raw_listing_20210118175114
49(id, full_name, ga)
50VALUES
51(1, 'Gord Thompson', 'test001')
52""")
53
54exec_sql("""\
55INSERT INTO intgn_party_test_load (
56  party_supertype, 
57  carrier_party_id, 
58  party_id, 
59  full_name, 
60  lda_actv_ind, 
61  lda_file_id) 
62SELECT  
63  'Agency' as s0,
64  0 as s1,
65  CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2,
66  CONCAT(full_name,'-',ga) as s3, 
67  lda_actv_ind, 
68  lda_file_id 
69FROM tst.raw_listing_20210118175114 
70ON DUPLICATE KEY 
71UPDATE  
72  full_name = VALUES(full_name), 
73  lda_actv_ind = VALUES(lda_actv_ind), 
74  lda_file_id = VALUES(lda_file_id) ;
75""")
76
77with engine.begin() as conn:
78    result = conn.execute(sa.text("SELECT party_id FROM tst.intgn_party_test_load ")).scalar()
79    print(result)  # 658636859098676345
80    print(f"{len(str(result))} digits")  # 18 digits
81