API tools faq
paste
Advertisement
MarkUa

Untitled

MarkUa
May 15th, 2019
429
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Python 9.38 KB | None | 0 0
raw download clone embed print report
  1. import pyodbc
  2. import random
  3.  
  4. cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=ACER-MOBILE\SQLEXPRESS;DATABASE=HockeyTeamAccounting')
  5.  
  6. # Using a DSN, but providing a password as well
  7.  
  8. # Create a cursor from the connection
  9. cities_countries = { "Україна" : ["Рівне","Маріуполь","Київ","Харків", "Дніпро","Кременчуг","Запоріжжя"],
  10.                     "Білорусь": ["Вітебськ", "Мінськ","Брест","Гомель","Орша","    Могильов"," Мозир"],
  11.                     "США" : ["Юта","Нью-Йорк","Фінікс","Анкоридж", "Мемфіс","Сан-Антоніо","Лас-Вегас"],
  12.                      "Канада": ["Оттава", "Едмунтон","Вінніпег","Ванкувер","Галіфакс","Монреаль","Шербрук","Лондон"]
  13.                      }
  14. teams_name_bu = ("Динамо","Леви","Арсенал","Адмірал","Бобри","Рисі","Зубри","Вовки")
  15. teams_name_сu = ("Пінгвіни","Індіанці","Койоти","Грізлі","Бобри","Броненосці","Вовки")
  16. date_range = range(1950,2010)
  17. cursor = cnxn.cursor()
  18. cursor.execute('SELECT * FROM Team')
  19. print("Hello")
  20. for row in cursor:
  21.     print(row)
  22.  
  23. #cursor.execute("Truncate table Team")
  24. rowcount = 0
  25.  
  26. while rowcount < 100:
  27.     for country in cities_countries.keys():
  28.         cursor.execute("SELECT Max(team_id) FROM Team")
  29.         rowcount = cursor.fetchone()[0]
  30.         print(str(rowcount) +" **** " )
  31.         if rowcount == 100:
  32.             break
  33.         try:
  34.             team_name = ""
  35.             if country in ["Україна","Білорусь"]:
  36.                 team_name = random.choice(teams_name_bu)
  37.             else:
  38.                 team_name = random.choice(teams_name_сu)
  39.             cursor.execute("insert into Team(team_country,team_city,team_name,team_fondation_year) "
  40.                            "values(?,?,?,?)",country,random.choice(cities_countries[country]),team_name ,random.choice(date_range))
  41.             cursor.commit()
  42.         except Exception as ex:
  43.             print(ex)
  44.     cursor.execute("SELECT MAX (team_id) FROM Team")
  45.     print(rowcount)
  46.     rowcount = cursor.fetchone()[0]
  47. cursor.execute("SELECT COUNT (team_id) FROM Team")
  48.  
  49. rowcount = cursor.fetchone()[0]
  50. print(rowcount)
  51.  
  52. cursor.execute('SELECT * FROM Team')
  53. name_bu = ["Петро","Василь","Пилип","Данило", "Руслан", "Ярослав" ,"Всеволод", "Микита"]
  54. surname_bu = ["Шевченко", "Василенко" ,"Дмитренко", "Іваненко", "Пилипенко", "Биков", "Стогній", "Пилипчук"]
  55. name_cu = ["Джон","Джек","Саймон" "Раймонд", "Мет", "Теодор" ,"Лео", "Чарльз"]
  56. surname_cu = ["Сноу", "Доу" ,"Кін", "Ремзі", "Вальс", "Кауфман", "Ріман", "МакАртур"]
  57. cursor.execute("SELECT COUNT (*) FROM Team")
  58. rowcount = cursor.fetchone()[0]
  59. cursor.execute("SELECT team_id,team_country From Team")
  60. import datetime
  61. for row in cursor.fetchall():
  62.         print(str(row.team_id) + "  "+ row.team_country)
  63.         notcorrect = True
  64.         while notcorrect:
  65.             try:
  66.                 trainer_name = ""
  67.                 trainer_surname = ""
  68.                 if row.team_country  in ["Україна", "Білорусь"]:
  69.                     trainer_name = random.choice(name_bu)
  70.                     trainer_surname = random.choice(surname_bu)
  71.                 else:
  72.                     trainer_name = random.choice(name_cu)
  73.                     trainer_surname = random.choice(surname_cu)
  74.                 cursor.execute("insert into Trainer(trainer_name,trainer_surname,trainer_country,birth_date,current_team_id) "
  75.                                "values(?,?,?,?,?)", trainer_name, trainer_surname, row.team_country,datetime.datetime(1960, 5, 17),row.team_id)
  76.                 cursor.commit()
  77.                 notcorrect = False
  78.             except Exception as ex:
  79.                 notcorrect = True
  80.                 print(ex)
  81.  
  82. cursor.execute("SELECT COUNT (*) FROM Team")
  83. print(cursor.fetchone()[0])
  84.  
  85.  
  86. stadion_names_bu = ["Олімпійський","Першотравневий","Словянський","Квітневий","Янтар","Вишневий","Імені Валерія Харламова"]
  87. stadion_names_cu = ["імені Вашингтона","імені Сміта","імені Вейна Ґрецкі","імені Боббі Орра"]
  88. for country in cities_countries.keys():
  89.     for city in  cities_countries[country]:
  90.         stad_name = ""
  91.         if country in ["Україна","Білорусь"]:
  92.             stad_name = random.choice(stadion_names_bu)
  93.         else:
  94.             stad_name = random.choice(stadion_names_cu)
  95.         cursor.execute("insert into Stadion(stadion_name,stadion_country,stadion_city) "
  96.                        "values(?,?,?)", stad_name,country,city)
  97.         cursor.commit()
  98. import itertools
  99. from faker import Faker
  100. import radar
  101. import copy
  102. def add_score():
  103.     general_score = range(0, 11)
  104.     general_home = random.choice(general_score)
  105.     general_guest = random.choice(general_score)
  106.     if general_home != general_guest:
  107.         return (general_home, general_guest, 0, 0, 0, 0)
  108.     overtime_score = range(0,6)
  109.     overtime_home = random.choice(overtime_score)
  110.     overtime_guest = random.choice(overtime_score)
  111.     if  overtime_guest!= overtime_home:
  112.         return (general_home, general_guest, overtime_home, overtime_guest, 0, 0)
  113.     bullit_home = 0
  114.     bullit_guest = 0
  115.     choose =  range(3,6)
  116.     while  bullit_home ==  bullit_guest:
  117.         bullit_home = random.choice(choose)
  118.         bullit_guest = random.choice(choose)
  119.  
  120.     return (general_home,general_guest,overtime_home,overtime_guest,bullit_home,bullit_guest)
  121.  
  122. def create_matches_for_tournament( cursor,country,tournament_id,start_time,end_time):
  123.     print(country)
  124.     cursor.execute('SELECT max(game_id) FROM Game')
  125.     print(cursor.fetchone()[0])
  126.     teams_ids = []
  127.     try:
  128.         cursor.execute('SELECT team_id FROM Team Where team_country = ?',country)
  129.         t = []
  130.         for r in cursor:
  131.             #print(r.team_id)
  132.             t.append(r.team_id)
  133.  
  134.         temp = list(itertools.combinations(t,2))
  135.         teams_ids = temp[:]#combination
  136.         for it in temp:
  137.             teams_ids.append((it[1],it[0]))
  138.         random.shuffle(teams_ids)
  139.         #print(teams_ids)
  140.     except Exception as e:
  141.         print("haha")
  142.         print(e)
  143.     for ids in teams_ids:
  144.         try:
  145.  
  146.             cursor.execute('Select * FROM Team Where team_id = ?', ids[0])
  147.  
  148.             team_city = ""
  149.             for r in cursor:
  150.                 team_city = r.team_city
  151.             #print( team_city)
  152.             cursor.execute('SELECT stadion_id FROM Stadion Where stadion_city = ?', team_city)
  153.             stadion_id = [r.stadion_id   for r in cursor][0]
  154.  
  155.             cursor.execute('SELECT trainer_id FROM Trainer Where current_team_id = ?', ids[0])
  156.             home_trainer_id = [r.trainer_id for r in cursor][0]
  157.  
  158.             cursor.execute('SELECT trainer_id FROM Trainer Where current_team_id = ?', ids[1])
  159.             guest_trainer_id = [r.trainer_id for r in cursor][0]
  160.             #, "
  161.             #" home_team_score,guest_team_score,overtime_home_team_score,overtime_guest_team_score,bullits_home_team_score,bullits_guest_team_score
  162.             #print(tournament_id)
  163.             print("hello")
  164.             home_score_general,guest_score_general,overtime_home_score,overtime_guest_score,bullits_home,bullits_guest = add_score()
  165.             print(str(home_score_general)+ " "+str(guest_score_general)+" "+str(overtime_home_score)+" "+str(overtime_guest_score)+" "+str(bullits_home)+" "+str(bullits_guest))
  166.             cursor.execute("insert into Game(tournament_id,home_team_id,guest_team_id,stadion_id,home_team_trainer_id,guest_team_trainer_id,start_time,"
  167.                            " home_team_score,guest_team_score,overtime_home_team_score,overtime_guest_team_score,bullits_home_team_score,bullits_guest_team_score) "
  168.                            "values(?,?,?,?,?,?,?, ?,?,?,?,?,?)", tournament_id, ids[0],ids[1],stadion_id,home_trainer_id,guest_trainer_id,radar.random_datetime(
  169.                 start=start_time,
  170.                 stop=end_time
  171.             ),home_score_general,guest_score_general,overtime_home_score,overtime_guest_score,bullits_home,bullits_guest)
  172.             cursor.commit()
  173.         except Exception as e:
  174.  
  175.             print(e)
  176.  
  177.  
  178.  
  179. import datetime
  180. tournament_name = {"Хокейна Ліга США":"локальний","Канадська Хокейна Ліга":"локальний","Українська Хокейна Ліга":"локальний","Білоруська Хокейна Ліга":"локальний"}
  181. countries = ["США","Канада","Білорусь","Україна"]
  182. count = 0
  183. for tournament_name,tournament_status in tournament_name.items():
  184.     cursor.execute("insert into Tournament(tournament_name,tournament_status,tournament_start,tournament_finish) "
  185.                    "values(?,?,?,?)", tournament_name, tournament_status, datetime.datetime(2018, 10, 1),datetime.datetime(2019, 4, 1))
  186.     cursor.commit()
  187.  
  188.     create_matches_for_tournament(cursor, countries[count], count+1, datetime.datetime(2018, 10, 1),datetime.datetime(2019, 4, 1))
  189.     count += 1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!