Data base connected by data not saving in mysql
# Note (Example is valid for Python v2 and v3)
from __future__ import print_function
import sys
# sys.path.insert(0, 'python{0}/'.format(sys.version_info[0]))
import mysql.connector
from mysql.connector.constants import ClientFlag
db = mysql.connector.connect(
host='127.0.0.1',
user="root",
password="Binay@1992",
database="retail")
print("cc")
class DBhelper:
def __init__(self):
self.con = db
# created brand table
brand = "create table if not exists brand(id int primary key, name varchar(200), country varchar(200))"
cur = self.con.cursor()
cur.execute(brand)
# creatd product table
product = "create table if not exists product(id int primary key, name varchar(200), " \
"brand_id int, foreign key(brand_id) references brand(id) )"
cur = self.con.cursor()
cur.execute(product)
# created warehouse table
warehouse = "create table if not exists warehouse(id int primary key, name varchar(200))"
cur = self.con.cursor()
cur.execute(warehouse)
# created good table
good = "create table if not exists good (id int primary key, warehouse_id int, product_id int, quantity int," \
"foreign key(warehouse_id) references warehouse(id), foreign key(product_id) references product(id) ) "
cur = self.con.cursor()
cur.execute(good)
# ---------inserting-------------------------
insert_brand = "insert ignore into brand(id, name, country) " \
"values" \
"('1', 'convers', 'America'), " \
"('2', 'reebok', 'England')," \
"('3', 'lodi', 'Span')," \
"('4', 'action','India' )," \
"('5', 'pollini', 'Itali')," \
"('6', 'adidas', 'Germane')," \
"('7', 'puma', 'Germane')," \
"('8', 'marc', 'Germane')," \
"('9', 'centro', 'Russia')," \
"('10', 'clark', 'England')"
cur = self.con.cursor()
cur.execute(insert_brand)
db.commit()
insert_product = "insert ignore into product(id, name, brand_id) " \
"values" \
"('11', 'shoo', '1'), " \
"('12', 'bag', '2')," \
"('13', 'sandal', '3')," \
"('14', 'shoo','4' )," \
"('15', 'bag', '5')," \
"('16', 'T-shart', '6')," \
"('17', 'cap', '7')," \
"('18', 'shoo', '8')," \
"('19', 'shoo', '9')," \
"('20', 'bag', '10')"
cur = self.con.cursor()
cur.execute(insert_brand)
db.commit()
insert_warehouse = "insert ignore into warehouse(id, name) values" \
"('501', 'warehouse_a')," \
"('502', 'warehouse_b')," \
"('503', 'warehouse_c')," \
"('504', 'warehouse_d')"
cur = self.con.cursor()
cur.execute(insert_warehouse)
db.commit()
cur.close()
insert_good = "insert ignore into good(id, warehouse_id, product_id, quantity) values" \
"('201', '501', '11', '150')," \
"('202', '504', '16', '50')," \
"('203', '501', '14', '200')," \
"('204', '501', '18', '0')," \
"('205', '501', '19', '30')," \
"('206', '502', '12', '300')," \
"('207', '502', '15', '20')," \
"('208', '502', '20', '0')," \
"('209', '503', '17', '50')," \
"('210', '504', '13', '500')"
cur = self.con.cursor()
cur.execute(insert_good)
db.commit()
cur.close()
print('created')
update_good = "update good set quantity = '250' where id = '1'"
cur = self.con.cursor()
cur.execute(update_good)
cur.close()
db.commit()
print(cur.rowcount, "record(s) affected")
heleper = DBhelper()
print('okay')