在pydev上为postgres数据库创建序列

Question

我试图在“GameID”列下生成从90001到90150（增量为1）的随机数，并在games表中生成它。我该怎么办？我听说一个序列可以工作，但我不知道这将如何工作。有什么想法吗

#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import csv
from itertools import count, cycle
from _tkinter import create
from setuptools.dist import sequence
path = r'C:\Users\sammy\Downloads\E0.csv'
with open(path, "r") as csvfile:
    readCSV = csv.reader(csvfile, delimiter=",")
    firstline = 1
    con = None
    con = psycopg2.connect("host='localhost' dbname='football' user='postgres' password='XXX'")   
    cur = con.cursor()
    cur.execute("DROP TABLE games")
    cur.execute("DROP TABLE teams")
    cur.execute("DROP TABLE referees")
    cur.execute("CREATE TABLE games (HomeTeamID VARCHAR, HomeTeam VARCHAR, AwayTeamID VARCHAR, AwayTeam VARCHAR, FTHG INTEGER, ATHG INTEGER, FTR VARCHAR, RefereeID VARCHAR, RefereeName VARCHAR, HY INTEGER, AY INTEGER)")
    cur.execute("CREATE TABLE teams (HomeTeamID VARCHAR, HomeTeam VARCHAR, AwayTeamID VARCHAR, AwayTeam VARCHAR)")
    cur.execute("CREATE TABLE Referees (RefereeID VARCHAR, RefereeName VARCHAR)")
    create sequence sq_demo
    start with 10001
    increment by 1
    maxvalue 15000
    minvalue 10001
    cache 3
    cycle
    order;

    try:
        for row in readCSV:
            if firstline:
                firstline=0
                continue 
            HomeTeamID = row[2]
            HomeTeam = row[2]
            AwayTeamID = row[3]
            AwayTeam = row[3]
            FTHG = row[4]
            ATHG = row[5]
            FTR = row[6]
            RefereeID = row[10]
            RefereeName = row[10]
            HY = row[19]
            AY = row[20]
            data1 = (HomeTeamID, HomeTeam, AwayTeamID, AwayTeam, FTHG, ATHG, FTR, RefereeID, RefereeName, HY, AY)
            data2 = (HomeTeamID, HomeTeam, AwayTeamID, AwayTeam)
            data3 = (RefereeID, RefereeName)
            query1 = "INSERT INTO games (HomeTeamID , HomeTeam, AwayTeamID, AwayTeam, FTHG, ATHG, FTR, RefereeID, RefereeName, HY, AY) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
            query2 = "INSERT INTO teams (HomeTeamID , HomeTeam, AwayTeamID, AwayTeam) VALUES (%s, %s, %s, %s);"
            query3 = "INSERT INTO Referees (RefereeID, RefereeName) VALUES (%s, %s);"
            print(HomeTeam)
            print(AwayTeam)
            print (FTHG)
            print (ATHG)
            print (FTR)
            print (RefereeID)
            print (RefereeName)
            print (HY)
            print (AY)
            cursor = con.cursor()
            cursor.execute(query1, data1)
            cursor.execute(query2, data2)
            cursor.execute(query3, data3)
    except psycopg2.DatabaseError as e:
        if con:
            con.rollback() 
            print ("Error %s % e", e)
            sys.exit(1) 
    finally:
        if con:
            con.commit()
            con.close()

print(" ".join(row))
out=open("new_data.csv", "w")
output = csv.writer(out)

for row in data1:
    output.writerow(row)

out.close()

Answer 1

I am trying to generate random numbers from 90001 to 90150 (increments of one) under a column "GameID" and have it generated in the games table

看起来这里不需要序列。 您可以运行此查询：

select i from generate_series(90001, 90150) as x(i) order by random()

在循环中插入的每个游戏中从中选择一个结果