Skip to content

Commit 5d28b21

Browse files
imsanjoykbimsanjoykb
committed
Python-mysql added
1 parent e72b94b commit 5d28b21

File tree

1 file changed

+335
-0
lines changed

1 file changed

+335
-0
lines changed

‎Python-MySql DB.py‎

Lines changed: 335 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,335 @@
1+
#!/usr/bin/env python
2+
# coding: utf-8
3+
4+
# ### Connect To Database Server
5+
6+
# In[1]:
7+
8+
9+
import mysql.connector
10+
11+
db = mysql.connector.connect(
12+
host="127.0.0.1",
13+
user="root",
14+
password="123456"
15+
)
16+
17+
if db.is_connected():
18+
print("Database Connected")
19+
20+
21+
# ### Create Database
22+
23+
# In[1]:
24+
25+
26+
import mysql.connector
27+
28+
db = mysql.connector.connect(
29+
host="127.0.0.1",
30+
user="root",
31+
password="123456"
32+
)
33+
34+
cursor = db.cursor()
35+
cursor.execute("CREATE DATABASE employee_data")
36+
37+
print("Database Created Successful !!!")
38+
39+
40+
# ### Create Table
41+
42+
# In[4]:
43+
44+
45+
import mysql.connector
46+
47+
db = mysql.connector.connect(
48+
host="127.0.0.1",
49+
user="root",
50+
password="123456",
51+
database="employee_data",
52+
)
53+
54+
cursor = db.cursor()
55+
sql = """CREATE TABLE customers (
56+
customer_id INT AUTO_INCREMENT PRIMARY KEY,
57+
name VARCHAR(255),
58+
address Varchar(255)
59+
)
60+
"""
61+
cursor.execute(sql)
62+
63+
print("Table Created Successful !!!")
64+
65+
66+
# ### Insert One Data
67+
68+
# In[1]:
69+
70+
71+
import mysql.connector
72+
73+
db = mysql.connector.connect(
74+
host="127.0.0.1",
75+
user="root",
76+
password="123456",
77+
database="employee_data",
78+
)
79+
80+
cursor = db.cursor()
81+
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
82+
val = ("Mr Taif", "Dhaka")
83+
cursor.execute(sql, val)
84+
85+
db.commit()
86+
87+
print("{} data added".format(cursor.rowcount))
88+
89+
90+
# ### Insert Many Data
91+
92+
# In[3]:
93+
94+
95+
import mysql.connector
96+
97+
db = mysql.connector.connect(
98+
host="127.0.0.1",
99+
user="root",
100+
password="123456",
101+
database="employee_data",
102+
)
103+
104+
cursor = db.cursor()
105+
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
106+
values = [
107+
("Shakib", "Magura"),
108+
("Tamin", "Ctg"),
109+
("Taskin", "Dhaka"),
110+
("Mushfiq", "Rajshahi")
111+
]
112+
113+
for val in values:
114+
cursor.execute(sql, val)
115+
116+
db.commit()
117+
118+
print("{} data added".format(cursor.rowcount))
119+
120+
121+
# ### Select
122+
123+
# In[6]:
124+
125+
126+
import mysql.connector
127+
128+
db = mysql.connector.connect(
129+
host="127.0.0.1",
130+
user="root",
131+
password="123456",
132+
database="employee_data",
133+
)
134+
135+
cursor = db.cursor()
136+
sql = "SELECT * FROM customers"
137+
cursor.execute(sql)
138+
139+
result = cursor.fetchone()
140+
141+
print(result)
142+
143+
144+
# ### Fetch All Data
145+
146+
# In[8]:
147+
148+
149+
import mysql.connector
150+
151+
db = mysql.connector.connect(
152+
host="127.0.0.1",
153+
user="root",
154+
password="123456",
155+
database="employee_data",
156+
)
157+
158+
cursor = db.cursor()
159+
sql = "SELECT * FROM customers"
160+
cursor.execute(sql)
161+
162+
results = cursor.fetchall()
163+
164+
for data in results:
165+
print(data)
166+
167+
168+
# ### Delete Data
169+
170+
# In[13]:
171+
172+
173+
import mysql.connector
174+
175+
db = mysql.connector.connect(
176+
host="127.0.0.1",
177+
user="root",
178+
password="123456",
179+
database="employee_data",
180+
)
181+
182+
cursor = db.cursor()
183+
sql = "DELETE FROM customers WHERE customer_id=%s"
184+
val = (4, )
185+
cursor.execute(sql, val)
186+
187+
db.commit()
188+
189+
print("{} data deleted".format(cursor.rowcount))
190+
191+
192+
# ### Update Data
193+
194+
# In[14]:
195+
196+
197+
import mysql.connector
198+
199+
db = mysql.connector.connect(
200+
host="127.0.0.1",
201+
user="root",
202+
password="123456",
203+
database="employee_data",
204+
)
205+
206+
cursor = db.cursor()
207+
sql = "UPDATE customers SET name=%s, address=%s WHERE customer_id=%s"
208+
val = ("ShakibAL", "Dhaka", 2)
209+
cursor.execute(sql, val)
210+
211+
db.commit()
212+
213+
print("{} data changed".format(cursor.rowcount))
214+
215+
216+
# ### curds_app
217+
218+
# In[ ]:
219+
220+
221+
import mysql.connector
222+
import os
223+
224+
db = mysql.connector.connect(
225+
host="127.0.0.1",
226+
user="root",
227+
password="123456",
228+
database="employee_data",
229+
)
230+
231+
232+
def insert_data(db):
233+
name = input("Enter Name: ")
234+
address = input("Enter Address: ")
235+
val = (name, address)
236+
cursor = db.cursor()
237+
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
238+
cursor.execute(sql, val)
239+
db.commit()
240+
print("{} data Inserted".format(cursor.rowcount))
241+
242+
243+
def show_data(db):
244+
cursor = db.cursor()
245+
sql = "SELECT * FROM customers"
246+
cursor.execute(sql)
247+
results = cursor.fetchall()
248+
249+
if cursor.rowcount < 0:
250+
print("There is not any data")
251+
else:
252+
for data in results:
253+
print(data)
254+
255+
256+
def update_data(db):
257+
cursor = db.cursor()
258+
show_data(db)
259+
customer_id = input("Choose id customer> ")
260+
name = input("New Name: ")
261+
address = input("New Address: ")
262+
263+
sql = "UPDATE customers SET name=%s, address=%s WHERE customer_id=%s"
264+
val = (name, address, customer_id)
265+
cursor.execute(sql, val)
266+
db.commit()
267+
print("{} data successfully changed".format(cursor.rowcount))
268+
269+
270+
def delete_data(db):
271+
cursor = db.cursor()
272+
show_data(db)
273+
customer_id = input("Choose id customer> ")
274+
sql = "DELETE FROM customers WHERE customer_id=%s"
275+
val = (customer_id,)
276+
cursor.execute(sql, val)
277+
db.commit()
278+
print("{} data successfully deleted".format(cursor.rowcount))
279+
280+
281+
def search_data(db):
282+
cursor = db.cursor()
283+
keyword = input("Keyword: ")
284+
sql = "SELECT * FROM customers WHERE name LIKE %s OR address LIKE %s"
285+
val = ("%{}%".format(keyword), "%{}%".format(keyword))
286+
cursor.execute(sql, val)
287+
results = cursor.fetchall()
288+
289+
if cursor.rowcount < 0:
290+
print("There is not any data")
291+
else:
292+
for data in results:
293+
print(data)
294+
295+
296+
def show_menu(db):
297+
print("=== APPLICATION DATABASE PYTHON ===")
298+
print("1. Insert Data")
299+
print("2. Show Data")
300+
print("3. Update Data")
301+
print("4. Delete Data")
302+
print("5. Search Data")
303+
print("0. GO Out")
304+
print("------------------")
305+
menu = input("Choose menu> ")
306+
307+
#clear screen
308+
os.system("clear")
309+
310+
if menu == "1":
311+
insert_data(db)
312+
elif menu == "2":
313+
show_data(db)
314+
elif menu == "3":
315+
update_data(db)
316+
elif menu == "4":
317+
delete_data(db)
318+
elif menu == "5":
319+
search_data(db)
320+
elif menu == "0":
321+
exit()
322+
else:
323+
print("Menu WRONG!")
324+
325+
326+
if __name__ == "__main__":
327+
while(True):
328+
show_menu(db)
329+
330+
331+
# In[ ]:
332+
333+
334+
335+

0 commit comments

Comments
 (0)