Python连接MySQL数据库
linux和windows下安装并启动mysql数据库python安装MySQLdb包，建议使用pip安装
pip使用方法如下：
yum install python-pip (优先安装python-pip才能使用pip)
pip类似于linux中的yum
pip install ipython （安装python补全工具）
pip uninstall ipython （使用pip卸载ipython）
pip list (查看安装了哪些包)
pip install ipython==1.2.1 (适用于python2.6的版本)
MySQL索引
索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引类别
1．普通索引
普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
2．唯一索引
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。这么做的好处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL 会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。
3．主索引
在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。
4．外键索引
如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5．复合索引
索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。
主键一定是唯一性索引，唯一性索引并不一定就是主键。
一个表中可以有多个唯一性索引，但只能有一个主键。
主键列不允许空值，而唯一性索引列允许空值。
索引可以提高查询的速度。
例子：
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
sql1 = '''alter table Teacher add primary key(TID);'''
sql2 = '''alter table Student add primary key(StdID);'''
sql3 = '''alter table Score add primary key(SID);'''
sql4 = '''alter table Course add primary key(CouID);'''
sql5 = '''alter table Score add index> #删除索引
sql6 = '''alter table Score dropindex> sql7 = '''explain select * from Score where StdID = 16213;'''
try:
cus = cnx.cursor()
cus.execute(sql1)
cus.close()
cus = cnx.cursor()
cus.execute(sql2)
cus.close()
cus = cnx.cursor()
cus.execute(sql3)
cus.close()
cus = cnx.cursor()
cus.execute(sql4)
cus.close()
cus = cnx.cursor()
cus.execute(sql5)
cus.close()
cus = cnx.cursor()
cus.execute(sql7)
result = cus.fetchall()
print(result)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
查看查询是否走索引需要加explain关键字，如mysql> explain select * from Score where StdID = 16213;
python连接mysql
例子：
定义一个connect_mysql函数，在函数中指定mysql连接参数，如下所示
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'zabbix_user',
'charset': 'utf8'
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
connect_mysql()
游标操作
例子：
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
#cursor():创建游标对象
cus = cnx.cursor()
sql= '''create table test(id int not null);insert into test(id) values (100);'''
try:
#excute(sql[, args]):执行一个数据库查询或命令
cus.execute(sql)
#close():关闭此游标对象
cus.close()
#提交游标对象
cnx.commit()
except Exception as e:
#回滚游标对象到commi之前
cnx.rollback()
raise e
finally:
cnx.close()
例子2：
#fetch函数的用法
#可以将例子1中的函数直接导入到此例子中使用，如下
#from 例子1 import connect_mysql
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
#创建游标对象
cus = cnx.cursor()
sql= '''select * from zabbix_user;'''
try:
cus.execute(sql)
#查看一行
result1 = cus.fetchone()
print('result1:')
print(result1)
#查看两行，接着上次查询开始
result2 = cus.fetchmany(2)
print('result2:')
print(result2)
#查看剩余所有行内容
result3 = cus.fetchall()
print('result3:')
print(result3)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
例子3：
#运行多个sql语句
import MySQLdb
def connect_mysql():
db_config = {
"host": "192.168.1.1",
"port": 3306,
"user": "xiang",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
try:
cnx = MySQLdb.connect(**db_config)
except Exception as e:
raise e
return cnx
if __name__ == "__main__":
sql = "select * from tmp;"
sql1 = "insert into tmp(id) value (%s);"
param = []
for i in xrange(100, 130):
param.append()
print(param)
cnx = connect_mysql()
cus = cnx.cursor()
print(dir(cus))
try:
cus.execute(sql)
cus.executemany(sql1, param)
result1 = cus.fetchone()
print("result1")
print(result1)
result2 = cus.fetchmany(3)
print("result2")
print(result2)
result3 = cus.fetchall()
print("result3")
print(result3)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
raise e
finally:
cnx.close()
数据库连接池的使用方法
python编程中可以使用MySQLdb进行数据库的连接及诸如查询/插入/更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。
python的数据库连接池包 DBUtils，可以使用pip安装
例子1：
import MySQLdb
from DBUtils.PooledDB import PooledDB
db_config = {
"host": "192.168.1.1",
"port": 3306,
"user": "zabbix",
"passwd": "123456",
"db": "python",
"charset": "utf8"
}
#连接池里的最少连接数为5
pool = PooledDB(MySQLdb, 5, **db_config)
#以后每次需要数据库连接就是用connection（）函数获取连接就好了
conn = pool.connection()
cur = conn.cursor()
SQL = "select * from tmp;"
r = cur.execute(SQL)
r = cur.fetchall()
print(r)
cur.close()
conn.close()
MySQL表的建立
例子：
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
cus = cnx.cursor()
# sql= '''insert into student(id, name, age, gender, score) values ('1001', 'ling', 29, 'M', 88), ('1002', 'ajing', 29, 'M', 90), ('1003', 'xiang', 33, 'M', 87);'''
student = '''create table Student(
StdID int not null,
StdName varchar(100) not null,
Gender enum('M', 'F'),
Age tinyint
)'''
course = '''create table Course(
CouID int not null,
CName varchar(50) not null,
TID int not null
)'''
score = '''create table Score(
SID int not null,
StdID int not null,
CID int not null,
Grade int not null
)'''
teacher = '''create table Teacher(
TID int not null,
TName varchar(100) not null
)'''
tmp = '''set @i := 0;
#mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。set这个是mysql中设置变量的特殊用法，当@i需要在select中使用的时候，必须加:
create table tmp as select (@i := @i + 1) as> '''
try:
cus.execute(student)
cus.execute(course)
cus.execute(score)
cus.execute(thearch)
cus.execute(tmp)
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
MySQL数据的建立
例子:
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
students = '''set @i := 10000;
#floor()函数表示去尾法取整；rand()函数代表的是从0到1取一个随机的小数；3+floor(rand() * 75)就代表的是：3到77的任意一个数字；concat()函数是一个对多个字符串拼接函数；sha1()是一个加密函数，sha1(rand())对生成的0到1的一个随机小数进行加密，转换成字符串的形式；
insert into Student select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 3 + floor(rand() * 75)), case floor(rand()*10) mod 2 when 1 then 'M' else 'F' end, 25-floor(rand() * 5)from tmp a, tmp b, tmp c, tmp d;
'''
course = '''set @i := 10;
insert into Course select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 40)),1 + floor(rand() * 100) from tmp a;
'''
score = '''set @i := 10000;
insert into Score select @i := @i +1, floor(10001 + rand()*10000), floor(11 + rand()*10), floor(1+rand()*100) from tmp a, tmp b, tmp c, tmp d;
'''
theacher = '''set @i := 100;
insert into Teacher select @i:=@i+1, substr(concat(sha1(rand()), sha1(rand())), 1, 5 + floor(rand() * 80)) from tmp a, tmp b;
'''
try:
cus_students = cnx.cursor()
cus_students.execute(students)
cus_students.close()
cus_course = cnx.cursor()
cus_course.execute(course)
cus_course.close()
cus_score = cnx.cursor()
cus_score.execute(score)
cus_score.close()
cus_teacher = cnx.cursor()
cus_teacher.execute(theacher)
cus_teacher.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
MySQL数据的查询
例子：
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.48.128',
'port': 3306,
'user': 'xiang',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
#()中的sql语句是把所有学生名字重复的学生都列出来；此sql是关联查询
sql = '''select * from Student where StdName in (select StdName from Student group by StdName having count(1)>1 ) order by StdName;'''
try:
cus = cnx.cursor()
cus.execute(sql)
result = cus.fetchall()
with codecs.open('select.txt', 'w+') as f:
for line in result:
f.write(str(line))
f.write('\n')
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
Python操作删除数据
例子：
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
#联结查询，将分数<60的老师查出，并针对不及格人数排序，只列出人数最多的5人；删除操作必须针对一个表，后面as test是作此操作
sql = '''delete from Teacher where TID in(
select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course
left join Score on Score.Grade < 60 and Course.CouID = Score.CouID
left join Teacher on Course.TID = Teacher.TID
group by Course.TID
order by count_teacher desc
limit 5)as test )
'''
try:
cus = cnx.cursor()
cus.execute(sql)
result = cus.fetchall()
cus.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
Python操作修改数据
例子：
import codecs
import MySQLdb
def connect_mysql():
db_config = {
'host': '192.168.1.1',
'port': 3306,
'user': 'zabbix',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
cnx = MySQLdb.connect(**db_config)
return cnx
if __name__ == '__main__':
cnx = connect_mysql()
#将小于5分的所有学生查出
sql = '''select *, (grade+60) as newGrade from Score where Grade <5;'''
#将所有小于五分的学生成绩都加60
update = '''update Score set grade = grade + 60 where grade < 5;'''
try:
cus_start = cnx.cursor()
cus_start.execute(sql)
result1 = cus_start.fetchall()
print(len(result1))
cus_start.close()
cus_update = cnx.cursor()
cus_update.execute(update)
cus_update.close()
cus_end = cnx.cursor()
cus_end.execute(sql)
result2 = cus_end.fetchall()
print(len(result2))
cus_end.close()
cnx.commit()
except Exception as e:
cnx.rollback()
print('error')
raise e
finally:
cnx.close()
页:
[1]