MySQL是Web世界中使用最广泛的数据库服务器,SQLite的特定是轻量级,可嵌入,但不能承受高并发访问,适合桌面和移动应用。而MySQL是为服务器端设计的数据库,能承受高并发访问,同时占用的内存也远远大于SQLite。此外,MySQL内部有多种数据库引擎,最常用的引擎是支持数据库事务的InnoDB。
这里就不详细介绍了,要是有不会安装的可以参考这篇博客:http://www.cnblogs.com/wj-1314/p/7573242.html
要想使python可以操作mysql 就需要MySQL-python驱动,它是python 操作mysql必不可少的模块。
下载地址:https://pypi.python.org/pypi/MySQL-python/
下载MySQL-python-1.2.5.zip 文件之后直接解压。进入MySQL-python-1.2.5目录:
>>python setup.py install
然后安装pymysql
pip install pymysql
测试非常简单,检查pymysql模块是否可以正常导入。(在操作数据库的时候,python2一般使用mysqldb,但是在python3中已经不再支持mysqldb了,我们可以用pymysql和mysql.connector。本文所有操作都是在python3的pymysql下完成的。)
没有报错提示MySQLdb模块找不到,说明安装OK
mysql> show databases; // 查看当前所有的数据库+--------------------+| Database |+--------------------+| information_schema || csvt || csvt04 || mysql || performance_schema || test |+--------------------+rows in set (0.18 sec) mysql> use test; //作用与test数据库Database changedmysql> show tables; //查看test库下面的表Empty set (0.00 sec) //创建user表,name 和password 两个字段mysql> CREATE TABLE user (name VARCHAR(20),password VARCHAR(20)); Query OK, 0 rows affected (0.27 sec) //向user表内插入若干条数据mysql> insert into user values(‘Tom‘,‘1321‘);Query OK, 1 row affected (0.05 sec) mysql> insert into user values(‘Alen‘,‘7875‘);Query OK, 1 row affected (0.08 sec) mysql> insert into user values(‘Jack‘,‘7455‘);Query OK, 1 row affected (0.04 sec) //查看user表的数据mysql> select * from user;+------+----------+| name | password |+------+----------+| Tom | 1321 || Alen | 7875 || Jack | 7455 |+------+----------+rows in set (0.01 sec) //删除name 等于Jack的数据mysql> delete from user where name = ‘Jack‘;Query OK, 1 rows affected (0.06 sec) //修改name等于Alen 的password 为 1111mysql> update user set password=‘1111‘ where name = ‘Alen‘;Query OK, 1 row affected (0.05 sec)Rows matched: 1 Changed: 1 Warnings: 0 //查看表内容mysql> select * from user;+--------+----------+| name | password |+--------+----------+| Tom | 1321 || Alen | 1111 |+--------+----------+rows in set (0.00 sec)
这里以流程图的方式展示python操作MySQL数据库的流程:
#coding=utf-8import MySQLdb# 打开数据库连接
conn= MySQLdb.connect( host=‘localhost‘, port = 3306, user=‘root‘, passwd=‘123456‘, db =‘test‘, )# 使用cursor()方法获取操作游标cur = conn.cursor() #创建数据表#cur.execute("create table student(id int ,name varchar(20),class varchar(30),age varchar(10))") #插入一条数据#cur.execute("insert into student values(‘2‘,‘Tom‘,‘3 year 2 class‘,‘9‘)") #修改查询条件的数据#cur.execute("update student set class=‘3 year 1 class‘ where name = ‘Tom‘") #删除查询条件的数据#cur.execute("delete from student where age=‘9‘")# 关闭游标cur.close()# 提交,不然无法保存新建或者修改的数据conn.commit()# 关闭数据库连接conn.close()
import pymysql conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘)cursor = conn.cursor()cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])conn.commit()cursor.close()conn.close() # 获取最新自增IDnew_id = cursor.lastrowid
import pymysql conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘)cursor = conn.cursor()cursor.execute("select * from hosts") # 获取第一行数据row_1 = cursor.fetchone() # 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# row_3 = cursor.fetchall() conn.commit()cursor.close()conn.close()
fetchone()方法可以帮助我们获得表中的数据,可是每次执行cur.fetchone() 获得的数据都不一样,换句话说我没执行一次,游标会从表中的第一条数据移动到下一条数据的位置,所以,我再次执行的时候得到的是第二条数据。fetchone()函数的返回值是单个的元组,也就是一行记录,如果没有,就返回null
fetchall() 函数的返回值是多个元组,即返回多个行记录,如果没有,返回的是()、
注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:
关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:
import pymysql conn = pymysql.connect(host=‘127.0.0.1‘, port=3306, user=‘root‘, passwd=‘123‘, db=‘t1‘) # 游标设置为字典类型cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)r = cursor.execute("call p1()") result = cursor.fetchone() conn.commit()cursor.close()conn.close()
通过上面execute()方法中写入纯的sql语句来插入数据并不方便。如:
>>>cur.execute("insert into student values(‘2‘,‘Tom‘,‘3 year 2 class‘,‘9‘)")
我要想插入新的数据,必须要对这条语句中的值做修改。我们可以做如下修改:
#coding=utf-8import MySQLdb conn= MySQLdb.connect( host=‘localhost‘, port = 3306, user=‘root‘, passwd=‘123456‘, db =‘test‘, )cur = conn.cursor() #插入一条数据sqli="insert into student values(%s,%s,%s,%s)"cur.execute(sqli,(‘3‘,‘Huhu‘,‘2 year 1 class‘,‘7‘)) cur.close()conn.commit()conn.close()
假如要一次向数据表中插入多条值呢?
executemany()方法可以一次插入多条值,执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数。
#coding=utf-8import MySQLdb conn= MySQLdb.connect( host=‘localhost‘, port = 3306, user=‘root‘, passwd=‘123456‘, db =‘test‘, )cur = conn.cursor() #一次插入多条记录sqli="insert into student values(%s,%s,%s,%s)"cur.executemany(sqli,[ (‘3‘,‘Tom‘,‘1 year 1 class‘,‘6‘), (‘3‘,‘Jack‘,‘2 year 1 class‘,‘7‘), (‘3‘,‘Yaheng‘,‘2 year 2 class‘,‘7‘), ]) cur.close()conn.commit()conn.close()
#!/usr/bin/env python# coding=utf-8 import pymysql def connectdb(): print(‘连接到mysql服务器...‘) # 打开数据库连接 # 用户名:hp, 密码:Hp12345.,用户名和密码需要改成你自己的mysql用户名和密码,并且要创建数据库TESTDB,并在TESTDB数据库中创建好表Student db = pymysql.connect("localhost","hp","Hp12345.","TESTDB") print(‘连接上了!‘) return db def createtable(db): # 使用cursor()方法获取操作游标 cursor = db.cursor() # 如果存在表Sutdent先删除 cursor.execute("DROP TABLE IF EXISTS Student") sql = """CREATE TABLE Student ( ID CHAR(10) NOT NULL, Name CHAR(8), Grade INT )""" # 创建Sutdent表 cursor.execute(sql) def insertdb(db): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 插入语句 sql = """INSERT INTO Student VALUES (‘001‘, ‘CZQ‘, 70), (‘002‘, ‘LHQ‘, 80), (‘003‘, ‘MQ‘, 90), (‘004‘, ‘WH‘, 80), (‘005‘, ‘HP‘, 70), (‘006‘, ‘YF‘, 66), (‘007‘, ‘TEST‘, 100)""" #sql = "INSERT INTO Student(ID, Name, Grade) # VALUES (‘%s‘, ‘%s‘, ‘%d‘)" % # (‘001‘, ‘HP‘, 60) try: # 执行sql语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: # Rollback in case there is any error print ‘插入数据失败!‘ db.rollback() def querydb(db): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 查询语句 #sql = "SELECT * FROM Student # WHERE Grade > ‘%d‘" % (80) sql = "SELECT * FROM Student" try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() for row in results: ID = row[0] Name = row[1] Grade = row[2] # 打印结果 print "ID: %s, Name: %s, Grade: %d" % (ID, Name, Grade) except: print "Error: unable to fecth data" def deletedb(db): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 删除语句 sql = "DELETE FROM Student WHERE Grade = ‘%d‘" % (100) try: # 执行SQL语句 cursor.execute(sql) # 提交修改 db.commit() except: print ‘删除数据失败!‘ # 发生错误时回滚 db.rollback() def updatedb(db): # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL 更新语句 sql = "UPDATE Student SET Grade = Grade + 3 WHERE ID = ‘%s‘" % (‘003‘) try: # 执行SQL语句 cursor.execute(sql) # 提交到数据库执行 db.commit() except: print ‘更新数据失败!‘ # 发生错误时回滚 db.rollback() def closedb(db): db.close() def main(): db = connectdb() # 连接MySQL数据库 createtable(db) # 创建表 insertdb(db) # 插入数据 print ‘\n插入数据后:‘ querydb(db) deletedb(db) # 删除数据 print ‘\n删除数据后:‘ querydb(db) updatedb(db) # 更新数据 print ‘\n更新数据后:‘ querydb(db) closedb(db) # 关闭数据库 if __name__ == ‘__main__‘: main()
练习题:
参考表结构:
用户类型
用户信息
权限
用户类型&权限
功能:
# 登陆、注册、找回密码
# 用户管理
# 用户类型
# 权限管理
# 分配权限
特别的:程序仅一个可执行文件
SQLite是一种嵌入式数据库,它的数据库就是一个文件。由于SQLite本身是C写的,而且体积很小。所以,经常被集成到各种应用程序中,甚至在iOS 和 Android 的APP中都可以集成。
Python就内置了SQLite3,所以在python中使用SQLite,不需要安装任何东西,直接使用。
在使用SQLite之前,我们先要搞清楚几个概念:
表是数据库中存放关系数据的集合,一个数据库里面通常都包含多个表,比如学生的表,班级的表,学校的表等等。表和表之间通过外键关联。
要操作关系数据库,首先需要连接到数据库,一个数据库连接成为Connection;
连接到数据库后,需要打开游标,称之为Cursor,通过Cursor执行SQL语句,然后获得执行结果。
Python定义了一套操作数据库的API接口,任何数据库要连接到Python,只需要提供符合Python标准的数据库驱动即可。
由于SQLite的驱动内置在Python标准库中,所以我们可以直接来操作SQLite数据库。
我们在Python交互式命令行实践一下:
# 导入SQLite驱动:>>> import sqlite3 # 连接到SQLite数据库# 数据库文件是test.db# 如果文件不存在,会自动在当前目录创建:>>> conn = sqlite3.connect(‘test.db‘) # 创建一个Cursor:>>> cursor = conn.cursor() # 执行一条SQL语句,创建user表:>>> cursor.execute(‘create table user (id varchar(20) primary key, name varchar(20))‘)<sqlite3.Cursor object at 0x10f8aa260> # 继续执行一条SQL语句,插入一条记录:>>> cursor.execute(‘insert into user (id, name) values (\‘1\‘, \‘Michael\‘)‘)<sqlite3.Cursor object at 0x10f8aa260> # 通过rowcount获得插入的行数:>>> cursor.rowcount1 # 关闭Cursor:>>> cursor.close() # 提交事务:>>> conn.commit() # 关闭Connection:>>> conn.close()
我们再试试查询记录:
>>> conn = sqlite3.connect(‘test.db‘)>>> cursor = conn.cursor() # 执行查询语句:>>> cursor.execute(‘select * from user where id=?‘, (‘1‘,))<sqlite3.Cursor object at 0x10f8aa340> # 获得查询结果集:>>> values = cursor.fetchall()>>> values[(‘1‘, ‘Michael‘)]>>> cursor.close()>>> conn.close()
使用Python的DB-API时,只要搞清楚connection 和cursor对象,打开后一定记得关闭,就可以放心使用。
使用cursor对象执行insert,update,delete语句时,执行结果由rowcount返回影响的行数,就可以拿到执行结果。
使用cursor对象执行select语句时,通过featchall() 可以拿到结果集,结果集是一个list,每个元素都是一个tuple,对应一行记录。
如果SQL语句带有参数,那么需要把参数按照位置传递给execute()方法,有几个?占位符就必须对应几个参数,例如:
ursor.execute(‘select * from user where name=? and pwd=?‘, (‘abc‘, ‘password‘))
练习:
import os, sqlite3 db_file = os.path.join(os.path.dirname(__file__), ‘test.db‘)print(db_file)# E:/backup/pycode/now/ProcessDataPreprocessing/code\test.db if os.path.isfile(db_file): os.remove(db_file) # 初始化数据conn = sqlite3.connect(db_file)cursor = conn.cursor()sql1 = ‘create table user(id varchar(20) primary key , name varchar(20), score int)‘cursor.execute(sql1)sql2 = "insert into user values (‘001‘,‘james‘, 99)"cursor.execute(sql2) sql3 = "insert into user values (‘002‘,‘durant‘, 99)"cursor.execute(sql3) cursor.close()conn.commit()conn.close()
查询操作:
# 查询记录:conn = sqlite3.connect(‘test.db‘)cursor = conn.cursor()# 执行查询语句:sql4 = ‘select * from user ‘cursor.execute(sql4)# 获得查询结果集:values = cursor.fetchall()print(values)cursor.close()conn.close()