1, 連接MySQL
import pyMySQL
連接MySQL
參數(shù)1:表示主機或IP地址
參數(shù)2: 表示MySQL 的用戶名
參數(shù)3: 表示MySQL 的密碼
參數(shù)4: 表示MySQL 的數(shù)據(jù)庫名
conn= pymysql.connect('localhost','root','root','mydb2')
conn=pymysql .connect('10.36.132.6','root','root','mydb2')
# 創(chuàng)建游標對象,可以執(zhí)行sql語句
cursor=conn.cursor()
sql 語句
sql='select version()'
執(zhí)行sql語句
cursor .execute(sql)
res = cursort.fetchone()
print(res)
# 關(guān)閉游標對象?
cursor.close()
# 關(guān)閉MySQL的連接
conn,close()
2.插入數(shù)據(jù)
import pymysql
conn=pymysql.connect('localhost','root','root','mydb2')
cursor = conn.cursor()
# 插入數(shù)據(jù)
sql = 'insert into person (name,age) values('aa',20)'
try:
? ? cursor.execute(sql)
# 提交事務(wù)
conn.commit()
except:
? ? # 回滾
conn,rollback()
cursor.close()
conn.close()
3.刪除數(shù)據(jù)
import pymysql
conn=pymysql.connect('localhost','root','root','mydb2')
cursor = conn.cursor()
刪除數(shù)據(jù)
sql='delete from person where id= 18'
try:
cursor.execute(sql)
#提交事務(wù)
conn.commit()
except:
# 回滾
conn.rollback()
cursor.close()
conn.close()
4.更新數(shù)據(jù)
import pymysql
conn= pymysql.connect('localhos','root','root','mydb2')
cursor= conn.cursor()
更新數(shù)據(jù)
sql='update person set age=30 where id = 20 '
try:
? ? cursor.execute(sql)
# 提交事務(wù)
conn.commit()
except:
# 回滾
conn.rollback()
cursor,close()
conn.close()
5.查詢數(shù)據(jù)
import pymysql?
conn=pymysql.connect('localhost','root','root','mydb2',charset='utf8')
cursor =conn.cursor()
# 查詢數(shù)據(jù)
sql='select * from person'
執(zhí)行sql
cursor.execute(sql)
# fetchone()? :每次查詢下一條數(shù)據(jù)
#res = cursor.fetchone()
# print (res)
res=cursor.fetchone()
print(res)
res=cursor.fetchone()
print(res)
fetchall()? # 所有數(shù)據(jù)
res== cursor.fetchall()
res=cursor.fetchmany(3)? # 前3條數(shù)據(jù)
for row in res:
print(row)
print(cursor.rowcount)? # 總的數(shù)據(jù)條數(shù)
cursor.close()
conn.close()
練習(xí),使用python操作MySQL數(shù)據(jù)庫
1.創(chuàng)建表格
create table tb_dept(
dno? int not null comment '編號',
dname varchar(10) not null comment '名稱',
dloc varchar(20) not null comment '所在地',
priamry key(dno)
);
insert into tb_dept values
(10,'會計部','北京')
(20,'研發(fā)部','成都')
(30,'銷售部','重慶')
(40,'運維部','深圳')
create tb_emp
(
eno? int not null comment '員工編號',
ename varchar(20) not null comment '員工姓名',
job varchar(20) not? null comment '員工職位',
mgr int comment '主管編號',
sal int not null comment '員工月薪',
comm int comment '每月補貼',
dno int comment '所在部門編號',
priamry key (eno)
);
alter? table tb_emp add constraint fk_emp_dno foreign key (dno)
references tb_dept(dno);
insert into tb_emp values
(7800, '張三豐', '總裁', null, 9000, 1200, 20),
(2056, '喬峰', '分析師', 7800, 5000, 1500, 20),
(3088, '李莫愁', '設(shè)計師', 2056, 3500, 800, 20),
(3211, '張無忌', '程序員', 2056, 3200, null, 20),
(3233, '丘處機', '程序員', 2056, 3400, null, 20),
(3251, '張翠山', '程序員', 2056, 4000, null, 20),
(5566, '宋遠橋', '會計師', 7800, 4000, 1000, 10),
(5234, '郭靖', '出納', 5566, 2000, null, 10),
(3344, '黃蓉', '銷售主管', 7800, 3000, 800, 30),
(1359, '胡一刀', '銷售員', 3344, 1800, 200, 30),
(4466, '苗人鳳', '銷售員', 3344, 2500, null, 30),
(3244, '歐陽鋒', '程序員', 3088, 3200, null, 20),
(3577, '楊過', '會計', 5566, 2200, null, 10),
(3588, '朱九真', '會計', 5566, 2500, null, 10);
2.添加一個部門
import pymysql
def main():
no = int(inport('編號:'))
name= inport('名字')
loc=inport ('所在地:')
# 創(chuàng)建數(shù)據(jù)庫連接對象
con= pymysql . connect(host='localhost',port=3306),
database='hrs',charset='utf8',
user = 'yourname',password='yourpass')
try:
? ? # 通過連接對象獲取游標
with con,cursor() as cursor:
? ? # 3.通過游標執(zhí)行sql并獲得執(zhí)行結(jié)果
result= cursor.execute(
insert into tb_dept values (%s,%s,%s)'',
(no,name,loc)
)
if result==1:
prrint('添加成功')
? ? ? ? # 操作成功提交事務(wù)
con.commit()
