· 8 years ago · Nov 29, 2017, 11:14 AM
1from __future__ import print_function
2
3import random
4import mysql.connector
5from mysql.connector import errorcode
6from collections import OrderedDict
7
8mainIngredients = ['pork','turkey','chiken','lamb','shrink','sardines','salmon','perch','tuna','whitefish','tofu']
9secondaryIngredients = ['rice','pasta','spaghetti','milk','cheese','paprika','salt','pepper','beans','potatoes','onion','spinach','mushroom','garlic','carrot','broccoli','cabbage','sugar','olive oils']
10
11
12
13recipes=[]
14
15DB_NAME = 'rfid'
16TABLES = OrderedDict()
17
18TABLES['Ingredient'] = (
19 "CREATE TABLE `Ingredient` ("
20 " `ID` int not null AUTO_INCREMENT primary key,"
21 " `name` varchar(30) not null "
22") ENGINE=InnoDB")
23
24TABLES['Recipe'] = (
25 "CREATE TABLE `Recipe` ("
26 " `ID` int not null primary key,"
27 " `name` varchar(30) "
28") ENGINE=InnoDB")
29
30TABLES['RecipeIngredient'] = (
31 "CREATE TABLE `RecipeIngredient` ("
32 "`RecipeID` int not null ,"
33 "`ingredientID` int not null ,"
34 "Primary key(RecipeID,ingredientID),"
35 "constraint `fk_ingredient` foreign key(`ingredientID`) references `Ingredient`(`ID`),"
36 "constraint `fk_recipe` foreign key(`RecipeID`) references `Recipe`(`ID`) "
37") ENGINE=InnoDB")
38
39TABLES['Restritions'] = (
40 "CREATE TABLE `Restritions` ("
41 "`nameReason` varchar(30) primary key,"
42 "`ingredientID` int not null,"
43"constraint fk_ingredientRestrict foreign key(ingredientID) references Ingredient(ID)"
44") ENGINE=InnoDB")
45
46TABLES['student'] = (
47 "CREATE TABLE `student` ("
48 " `studentID` int NOT NULL AUTO_INCREMENT primary key,"
49 " `birth_date` date NOT NULL,"
50 " `first_name` varchar(30) NOT NULL,"
51 " `last_name` varchar(30) NOT NULL,"
52 " `address` varchar(30) NOT NULL,"
53 " `restritionsname` varchar(30),"
54 "constraint `fk_restritions` foreign key(`restritionsname`) references `Restritions`(`nameReason`)"
55 ") ENGINE=InnoDB")
56
57
58for i in range(0,1000):
59 tmp = []
60 tmp.append(random.choice(mainIngredients))
61 name = tmp[0];
62 secondary = random.randint(3,6)
63 for _ in range(0, secondary):
64 choice = random.choice(secondaryIngredients)
65 if choice not in tmp:
66 tmp.append(choice)
67 name += choice[0];
68 if sorted(tmp) not in sorted(recipes):
69 recipes.append([tmp,name])
70
71print(recipes[0])
72
73cnx = mysql.connector.connect(user='root', password='root',
74 host='127.0.0.1',
75 )
76
77
78cursor = cnx.cursor(buffered=True)
79try:
80 cursor.execute("drop schema rfid")
81except:
82 pass
83
84def create_database(cursor):
85 try:
86 cursor.execute(
87 "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
88 except mysql.connector.Error as err:
89 print("Failed creating database: {}".format(err))
90 exit(1)
91
92try:
93 cnx.database = DB_NAME
94except mysql.connector.Error as err:
95 if err.errno == errorcode.ER_BAD_DB_ERROR:
96 create_database(cursor)
97 cnx.database = DB_NAME
98 else:
99 print(err)
100 exit(1)
101for name, ddl in TABLES.iteritems():
102 try:
103 print("Creating table {}: ".format(name), end='')
104 cursor.execute(ddl)
105 except mysql.connector.Error as err:
106 if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
107 print("already exists.")
108 else:
109 print(err.msg)
110 else:
111 print("OK")
112for i in mainIngredients:
113 cursor.execute('INSERT INTO Ingredient (name) VALUES ("{}")'.format(i))
114for i in secondaryIngredients:
115 cursor.execute('INSERT INTO Ingredient (name) VALUES ("{}")'.format(i))
116cnx.commit()
117
118
119for index, item in enumerate(recipes):
120 cursor.execute('INSERT INTO Recipe(id,name) VALUES ("{}","{}")'.format(index,item[1]))
121 for j in item[0]:
122 cursor.execute("SELECT * FROM Ingredient where name ='{}'".format(j))
123 for (id,name) in cursor:
124 cursor.execute('INSERT INTO RecipeIngredient (RecipeID,ingredientID) VALUES ({},{})'.format(index,id))
125
126cnx.commit()
127cursor.close()
128cnx.close()