17 Inserting a sequence of numbers in Python.sql

-- Write a Python program to insert a sequence of 300 pseudorandom numbers into a database table named pythonseq with the following schema:

CREATE TABLE pythonseq (iter INTEGER, val INTEGER);

-- The following Python code will generate your series of pseudo-random numbers:

number = 801200
for i in range(300) :
    print(i+1, number)
    value = int((value * 22) / 7) % 1000000

-- install psycopg2 library

pip3 install psycopg2

-- Importe psycopg2 and hidden 

import psycopg2
import hidden
 
-- Load database connection details from a separate file

secrets = hidden.secrets()

-- Connect to the PostgreSQL database

conn = psycopg2.connect(host=secrets['host'],
                        port=secrets['port'],
                        database=secrets['database'],
                        user=secrets['user'],
                        password=secrets['pass'],
                        connect_timeout=3)
-- Create a cursor to interact with the database

cur = conn.cursor()

-- Drop the pythonseq table if it exists

sql = 'DROP TABLE IF EXISTS pythonseq CASCADE;'
cur.execute(sql)

-- Create the pythonseq table

sql = 'CREATE TABLE pythonseq (iter INTEGER, val INTEGER);'
cur.execute(sql)

 -- Commit the changes to the database
 
conn.commit()
 
-- Set the initial value

number = 801200

-- Generate and insert the sequence of 300 pseudorandom numbers into the pythonseq table

for i in range(300):
    -- Print the current iteration and the current value
    print(i+1, number)
    -- Calculate the next value based on the pseudorandom formula
    value = int((number * 22) / 7) % 1000000
    -- SQL query to insert the current iteration and value into the pythonseq table
    SQL = "INSERT INTO pythonseq (iter, val) VALUES (%s, %s);"
    -- Execute the SQL query with the current iteration and value
    cur.execute(SQL, (i+1, number))
    -- Update the number for the next iteration
    number = value
 
1 801200
2 518057
3 628179
4 974276
5 62010
6 194888
7 612505
8 925015
9 907190
10 851168
11 675099
12 121739
13 382608
14 202482
15 636372
16 26
17 81
18 254
19 798
20 2508
21 7882
22 24772
23 77854
24 244684
25 769006
26 416876
27 310181
28 974854
29 63826
30 200596
31 630444
32 981395
33 84384
34 265206
35 833504
36 619584
37 947264
38 977115
39 70932
40 222929
41 700634
42 201992
43 634832
44 995186
45 127727
46 401427
47 261627
48 822256
49 584233
50 836160
51 627931
52 973497
53 59562
54 187194
55 588324
56 849018
57 668342
58 100503
59 315866
60 992721
61 119980
62 377080
63 185108
64 581768
65 828413
66 603583
67 896975
68 819064
69 574201
70 804631
71 528840
72 662068
73 80785
74 253895
75 797955
76 507858
77 596125
78 873535
79 745395
80 342670
81 76962
82 241880
83 760194
84 389181
85 223140
86 701297
87 204076
88 641381
89 15768
90 49556
91 155747
92 489490
93 538397
94 692104
95 175184
96 550578
97 730388
98 295505
99 928730
100 918865
101 887861
102 790420
103 484177
104 521699
105 639625
106 10250
107 32214
108 101244
109 318195
110 41
111 128
112 402
113 1263
114 3969
115 12474
116 39204
117 123212
118 387237
119 217030
120 682094
121 143724
122 451704
123 419641
124 318871
125 2166
126 6807
127 21393
128 67235
129 211310
130 664117
131 87224
132 274132
133 861557
134 707750
135 224357
136 705122
137 216097
138 679162
139 134509
140 422742
141 328617
142 32796
143 103073
144 323943
145 18106
146 56904
147 178841
148 562071
149 766508
150 409025
151 285507
152 897307
153 820107
154 577479
155 814934
156 561221
157 763837
158 400630
159 259122
160 814383
161 559489
162 758394
163 383524
164 205361
165 645420
166 28462
167 89452
168 281134
169 883564
170 776915
171 441732
172 388300
173 220371
174 692594
175 176724
176 555418
177 745599
178 343311
179 78977
180 248213
181 780098
182 451736
183 419741
184 319186
185 3156
186 9918
187 31170
188 97962
189 307880
190 967622
191 41097
192 129162
193 405937
194 275802
195 866806
196 724247
197 276204
198 868069
199 728216
200 288678
201 907273
202 851429
203 675919
204 124316
205 390707
206 227936
207 716370
208 251448
209 790265
210 483690
211 520168
212 634813
213 995126
214 127538
215 400833
216 259760
217 816388
218 565790
219 778197
220 445762
221 400966
222 260178
223 817702
224 569920
225 791177
226 486556
227 529176
228 663124
229 84104
230 264326
231 830738
232 610890
233 919940
234 891240
235 801040
236 517554
237 626598
238 969308
239 46396
240 145816
241 458278
242 440302
243 383806
244 206247
245 648204
246 37212
247 116952
248 367563
249 155198
250 487765
251 532975
252 675064
253 121629
254 382262
255 201394
256 632952
257 989277
258 109156
259 343061
260 78191
261 245743
262 772335
263 427338
264 343062
265 78194
266 245752
267 772363
268 427426
269 343338
270 79062
>>> cur.execute(sql)
271 248480
272 780937
273 454373
274 428029
275 345234
276 85021
277 267208
278 839796
279 639358
280 9410
281 29574
282 92946
283 292116
284 918078
285 885388
286 782648
287 459750
288 444928
289 398345
290 251941
291 791814
292 488558
293 535468
294 682899
295 146254
296 459655
297 444630
298 397408
299 248996
300 782558

-- Commit the final changes to the database

conn.commit()

-- Close the cursor and the database connection

cur.close()