Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Sign up
Appearance settings

Commit 5d28b21

Browse files
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)

AltStyle によって変換されたページ (->オリジナル) /