python - pro - 19 - MySQL
Beginner - Intermediate - Advanced - Pro - MySQL - SQLite
MySQL -
Ben XAMPP ile baglanti kuracagim
pip install mysql-connector # kullanilmiyor !!!!! deprecated diyor pypi.org
pip install mysql-connector-python # XAMPP icin
pip install pymysql - emin degilim ama yaptim
Make connection
import mysql.connector
mydb = mysql.connector.connect(
host = "aloryahost", # web de sayfamiz 192.45.77.96
username= "zagor_tenay",
password = "FerhanSensoy@@qw",
#database="alorya_db" - db`ye ulasim test edilebilir
)
print (mydb.is_connected)
mydb.close()
# her sey yolundaysa
<bound method CMySQLConnection.is_connected of
<mysql.connector.connection_cext.CMySQLConnection object
at 6s7812345FGYH68W4V0>> -- bu sayimhayali oylesine uydurdum.
Sizde de boyle bir kod cikacak - o zaman baglanti tamam demek
Create Database
import mysql.connector
mydb = mysql.connector.connect(
host = "aloryahost", # web de sayfamiz 192.45.77.96
username= "gainsbourg",
password = "Fantomas_LouisDeFunes$$",
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
mydb.close()
Show databases
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
#
('Seramik',)
('OperaBale',)
('Heykel',)
('AkrilikResim',)
('Tiyatro',)
Create Table
database="mydatabase" # bir database ile connection kurulmali
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(45), adress VARCHAR(70))")
#
iki field li bir adet tablo yarattik
insert into table
import mysql.connector
def insertProduct():
connect_db = mysql.connector.connect(
host = "aloryahost",
username= "root",
password = "KemalSunal@2024",
database="mydatabase")
mycursor=connect_db.cursor()
sql="INSERT INTO product (image_url,namex,price,desco) VALUES (%s,%s,%s,%s)"
values=('1.jpg','S5',1000.00,'yo man good')
mycursor.execute(sql,values)
try:
connect_db.commit()
except mysql.connector.Error as errdb:
print("hata: ",errdb)
finally:
connect_db.close()
print(mycursor.rowcount,"db baglanti off")
insertProduct()
note: field adlari `name` veya `desc` adlarini taşımamalı. Bunlar sistemde kullanılan adlar
uygulama
def insertProductMulti(listprod):
connect_db = mysql.connector.connect(
host = "aloryahost",
username= "root",
password = "AdileNasit@$123,
database="comp_mang")
mycursor=connect_db.cursor()
sql="INSERT INTO product (namex,price,image_url,desco) VALUES (%s,%s,%s,%s)"
values=listprod
mycursor.executemany(sql,values)
try:
connect_db.commit()
print(f'{mycursor.rowcount} tane kayit eklendi')
print(f'son eklenen kayit id:{mycursor.lastrowid}')
except mysql.connector.Error as errdb:
print("hata: ",errdb)
finally:
connect_db.close()
print("db baglanti off")
listprod=[]
while True:
nameInput = input("urun adi: ")
priceInput = float(input("urun fiyat: "))
descoInput = input("urun description: ")
image_urlInput = input("urun image url: ")
listprod.append((nameInput,priceInput,image_urlInput,descoInput))
resultfinal = input('devam etmek istiyor musunuz? (e/h)')
if resultfinal =='h':
print ('kayitlariniz db ye aktariliyor...')
print(listprod)
insertProductMulti(listprod)
break
connection ayri dosyada
from _19_2_mydb_connection import connect_db
#
def insertProduct(namex,price,image_url,desco):
mycursor=connect_db.cursor()
Class ile
single add
import mysql.connector
from _19_2_mydbconnection import connect_db
from datetime import datetime
class Student:
connectionclass= connect_db
mycursor=connectionclass.cursor()
def __init__(self,studentid,name,surname,birthdate,gender):#tekil data ekleme instance`i
self.studentid=studentid
self.name=name
self.surname=surname
self.birthdate=birthdate
self.gender=gender
def saveStudent(self):#tekil data ekleme instance`i
sql="INSERT INTO student (studentid,name,surname,birthdate,gender) VALUES (%s,%s,%s,%s,%s)"
studentvalue=(self.studentid,self.name,self.surname,self.birthdate,self.gender)
Student.mycursor.execute(sql,studentvalue)
try:
Student.connectionclass.commit()#class seviyesinden gormesi lazim onun icin basina Student. ekledik
print(f'{Student.mycursor.rowcount} tane kayit eklendi')
print(f'son eklenen kayit id:{Student.mycursor.lastrowid}')
except mysql.connector.Error as errdb:
print("hata: ",errdb)
finally:
Student.connectionclass.close()
print("db baglanti off")
ahmet=Student("402","Ricky","yilmaz",datetime(2005,5,17),"e")
ahmet.saveStudent()
multi add
import mysql.connector
from ddl_mydbconnection import connect_db
from datetime import datetime
class StudentClass:
connectionclass= connect_db
mycursor=connectionclass.cursor()
def __init__(self,studentid,name,surname,birthdate,gender):#tekil data ekleme instance`i
self.studentid=studentid
self.name=name
self.surname=surname
self.birthdate=birthdate
self.gender=gender
def saveStudent(self):#tekil data ekleme instance`i
sql="INSERT INTO student (studentid,name,surname,birthdate,gender) VALUES (%s,%s,%s,%s,%s)"
studentvalue=(self.studentid,self.name,self.surname,self.birthdate,self.gender)
StudentClass.mycursor.execute(sql,studentvalue)
try:
StudentClass.connectionclass.commit()#class seviyesinden gormesi lazim onun icin basina Student. ekledik
print(f'{StudentClass.mycursor.rowcount} tane kayit eklendi')
print(f'son eklenen kayit id:{Student.mycursor.lastrowid}')
except mysql.connector.Error as errdb:
print("hata: ",errdb)
finally:
StudentClass.connectionclass.close()
print("db baglanti off")
@staticmethod #bu instance method olmadigini belirtiyor - onun icin self parametresini vermiyoruz
def saveStudents(students):
sqlManyStudents="INSERT INTO student (studentid,name,surname,birthdate,gender) VALUES (%s,%s,%s,%s,%s)"
ManyStudentValues=students
StudentClass.mycursor.executemany(sqlManyStudents,ManyStudentValues)
try:
StudentClass.connectionclass.commit()#class seviyesinden gormesi lazim onun icin basina Student. ekledik
print(f'{StudentClass.mycursor.rowcount} tane kayit eklendi')
print(f'son eklenen kayit id:{StudentClass.mycursor.lastrowid}')
except mysql.connector.Error as errdb:
print("hata: ",errdb)
finally:
StudentClass.connectionclass.close()
print("db baglanti off")
ogrenciler=[
("103","John","yilmaz",datetime(2005,5,17),"e"),
("104","Mick","can",datetime(2005,6,21),"e"),
("105","Jenny","tan",datetime(2005,7,7),"k"),
("106","Catherine","taner",datetime(2005,9,23),"k"),
("107","Ivan","tok",datetime(2005,7,27),"e"),
("108","Lazslo","veli",datetime(2005,8,25),"e")
]
StudentClass.saveStudents(ogrenciler)
Select
import _mysql_connector
from _19_2_mydbconnection import connect_db
def getproducts():
moicursor = connect_db.cursor()
moicursor.execute('Select * From Product')
resultados= moicursor.fetchall()
print(resultados)
getproducts()
#
[(1, '1.jpg', 'S5', Decimal('1000'), 'yo man good'), (2, '', 'iph6', Decimal('4'), ''),
(3, 'majo', 'iph8', Decimal('3800'), 'ko.jpg'), (4, 'imia.jk', 'hgggghj',
Decimal('9789787'), 'dedede'), (5, 'imuy.tgd', 'addassd', Decimal('324'), 'dedefr'),
(6, 'ert.34s', 'shoshon', Decimal('2345'), 'weldone'), (7, 'ert.34f', 'shoshon',
Decimal('23456'), 'fantomas'), (8, 'ert.34f', 'shoshon',
Decimal('23456'), 'fantomas')]
veya
#print(resultados)
for productamos in resultados:
print(productamos)
getproducts()
#(1, '1.jpg', 'S5', Decimal('1000'), 'yo man good')
(2, '', 'iph6', Decimal('4'), '')
(3, 'majo', 'iph8', Decimal('3800'), 'ko.jpg')
(4, 'imia.jk', 'hgggghj', Decimal('9789787'), 'dedede')
(5, 'imuy.tgd', 'addassd', Decimal('324'), 'dedefr')
(6, 'ert.34s', 'shoshon', Decimal('2345'), 'weldone')
(7, 'ert.34f', 'shoshon', Decimal('23456'), 'fantomas')
(8, 'ert.34f', 'shoshon', Decimal('23456'), 'fantomas')
veya daha kisitli
for productamos in resultados:
print(f"name: {productamos[2]} - price: {productamos[3]}")
getproducts()
#name: S5 - price: 1000
name: iph6 - price: 4
name: iph8 - price: 3800
name: hgggghj - price: 9789787
name: addassd - price: 32461
name: shoshon - price: 2345
name: shoshon - price: 211456
name: shoshon - price: 28797
aynisi - ama * ile dusuk performans, asagidaki sekil daha iyi performans
moicursor.execute('Select namex,price From Product')
....
for productamos in resultados:
print(f"name: {productamos[0]} - price: {productamos[1]}")
Where
moicursor.execute('Select id,namex,price From Product Where id=4')
...........
for productamos in resultados:
print(f"id: {productamos[0]} name: {productamos[1]} - price: {productamos[2]}")
#id: 4 name: hgggghj - price: 9789787
Where Like - contains
moicursor.execute("Select id,namex,price From Product Where name LIKE '%Samsung%'")
and
Where name LIKE '%Samsung%' and price>3000")
starts with
Where name LIKE 'Samsung%'
ends with
Where name LIKE '%Samsung'
input ile alma
def getProductById (id):
#parantez icine veri koydugumuzda
# bunun anlami=disaridan veri alacagiz
.....
sql = "Select * From Product Where id=%s" #%s bir placeholder
params = (id,)
cursor.execute(sql,params)
.......
getProductById(1) #input var gibi ama statik
order by
sql = 'Select * From Product Order By name'
descending:
sql = 'Select * From Product Order By name DESC'
Agregate -hesaplama
count
sql = 'Select COUNT(*) From Product'
average
sql = 'Select AVG(price) From Product'
sum
sql = 'Select SUM(price) From Product'
subquery - max price merchandise name:
sql = 'Select name From Product Where
price=(Select AVG(price) From Product'
Update
sql = "Update product SET name='Samsung S10' Where id=1"
cursor.execute(sql)
Delete
sql = 'Delete from product Where id=6'
cursor.execute(sql)
Comments
Post a Comment