#!/usr/bin/python import psycopg2 import random from random import randrange, randint from datetime import timedelta, datetime names = open("names.txt").readlines() industries = open("industries.txt").readlines() def rletter(): alf = "abcdefghijklmnopqrstuvwxyz" j = randint(0, len(alf)-1) return alf[j] def rname(): global names return names[ randint(0, len(names)-1) ].strip() def rindus(): global industries return industries[ randint(0, len(industries)-1) ].strip() def rcomp(): n = rname() x = randint(1,4) if x == 1: return rname() + " & " + rname() elif x==2: return rname() + " & Sons" elif x==3: return rname() + " & Co." else: return rname() + " Inc." def rdate(): start = datetime.strptime("2011 01 01", "%Y %m %d") end = datetime.strptime("2011 12 31", "%Y %m %d") delta = end - start int_delta = (delta.days * 24 * 60 * 60) + delta.seconds random_second = randrange(int_delta) return datetime.strftime(start + timedelta(seconds=random_second), "%Y-%m-%d") def testpg(): ncust = 200 conn = psycopg2.connect(dbstr()) cur = conn.cursor() cur.execute("delete from CUST;") for i in range(1,ncust+1): cur.execute("insert into CUST (cid, name, area, emp, indus) values (%s, %s, %s, %s, %s)", (i, rcomp(), randint(1,3) * 10, randint(1, 500), rindus())) # cur.execute("SELECT cid, name, area, emp, indus from CUST;") # for rec in cur: # (cid, name, area, emp, indus) = rec # print cid, name, area, emp, indus cur.execute("delete from PROD;") i = 1 for line in open("parts.txt").readlines(): cur.execute("insert into PROD (pid, name, weight, color, price) values (%s, %s, %s, %s, %s)", (i, line.strip(), randint(1,100) / 10.0, ("Gray", "Black", "White")[ randint(0,2) ], randint(1, 20) / 5.0)) i += 1 nprod = i-1 # cur.execute("SELECT pid, name, weight, color, price from PROD;") # for rec in cur: # (pid, name, weight, color, price) = rec # print pid, name, weight, color, price cur.execute("delete from EMP;") for i in range(1,11): if i >= 9: salary = 15000 else: salary = randint(18000, 24000) cur.execute("insert into EMP (eid, name, salary) values (%s, %s, %s)", (i, rname(), salary)) cur.execute("delete from SALES;") for i in range(200): cur.execute("insert into SALES (pid, cid, emp, sdate, qty) values (%s, %s, %s, %s, %s)", (randint(1, nprod+1), randint(1, ncust+1), random.sample([1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,10], 1)[0], rdate(), randint(1,11))) cur.close() conn.commit() conn.close() def main(): testpg() def dbstr(): return open("db.txt").read() if __name__ == '__main__': main()