python-对于mysql数据库的操作

python操作mysql数据库

问题：DDL，DCL,DML的区别？

语言与框架：jdbc/odbc操作数据库

   java(ibatis/hibernate/jpa)操作数据库

客户端工具：navicat for mysql

 

步骤：

1.连接上mysql

命令：mysql -u用户名 -p密码 -P端口 -hip地址 数据库名称

举例：mysql -uroot -proot -P3306 -h127.0.0.1 mysql

2.创建数据名称

命令：create database 数据库名称

举例：create database mydb

3.切换数据库

命令：use 数据库名称

举例：use mydb

4.创建表

命令：create table 表名(字段1名称 类型和长度 修饰符 是否可以为空，

     字段2名称 类型和长度 修饰符 是否可以为空)

举例：create table userInfo(id int primary key not null,userName varchar(10) not null,userAge int not null,userSex varchar(10) not null,userAddress varchar(100) not null)

 

5.插入表

命令：insert into 表名 values(字段1，字段2，字段N)

举例：insert into userInfo values(1,'张三',20,'男','','北京');

insert into userInfo values(2,'李四',21,'女','上海');

6.查询表

命令：select *或者列名 from 表名 where 列名1=xxx and/or 列名2=xxx

举例：select * from userInfo;

 

7.Python操作mysql数据库

ipython+mysqlDBData.dll

python+MySQLdb,比如oracle(CX_oracle)

  1)一般理论上操作流程

    begin---建立连接(connection)-》操作数据（增/删/改查）-》获取结果

 -》关闭连接---end

  2）实际操作流程

    begin---建立连接(connection)-》游标-》操作数据（增/删/改查）-》获取结果-》

 -》提交事务-》关闭游标-》关闭连接---end

  3)前提准备，需要安装MySQLDB

  4）练习：使用python向userInfo表中插入一行数据

 

数据库数据预制脚本

mysql -uroot -proot -P3306 -h127.0.0.1 mysql

create database mydb;

use mydb;

create table userInfo(id int primary key not null,userName varchar(10) not null,userAge int not null,userSex varchar(10) not null,userAddress varchar(100) not null);

insert into userInfo values(1,'张三',20,'男','北京');

代码示例：

import MySQLdb



#begin---建立连接(connection)-》游标-》操作数据（增/删/改查）-》获取结果-》

#    -》提交事务-》关闭游标-》关闭连接---end



#插入并查询所有结果

def select():

    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")

    curson = conn.cursor()

    result =  curson.execute("select * from userInfo")

    print "result: ",result

    curson.close()

    conn.close()

if __name__ == '__main__':

    select()

 

8.python与mysql能解决什么问题？

  1) 功能测试-----模拟业务数据

  2）自动化测试----自动化用例的数据预制

  3）性能测试----大量数据（<百万级），如果千万级数据推荐使用load

  4）其他---测试桩开发、程序数据开发

 

9.插入和查询

  插入：支持两种方式

   1）单条插入  execute("insert into userInfo values(5,'测试人员2',25,'女','北京')")，也可以使用模板插入，例如：

      cursor.execute("insert into userInfo values(%s,%s,%s,%s,%s)",(7,'测试人员4',27,'女','北京'))

   2) 多条插入，需要使用占位符模板，通过元组列表来实现，例如：

   模板语句：insertSql2 = insert into userInfo values(%s,%s,%s,%s,%s)

   举例：executemany(insertSql2,元组列表），例如： executemany(insertSql2,[(8,'测试人员5',28,'女','北京'),(9,'测试人员6',29,'女','北京'),(10,'测试人员7',30,'女','北京')])

  查询：查询当前游标指定的一行，查询所有行，查询当前游标以下多少行

 1）查询当前游标的一行，cursor.fetchone()

 2) 查询所有行，基于当前游标来说 ，cursor.fetchall()

 3）查询当前游标以下的行数，cursor.fetchman(3) #比如3行

  返回值是一个元组，多个是一个元组内包含元组，

  游标是从0开始，数据取完后，游标的值等于数据总行，后面获取的值为None

10.修改和删除

   删除：delete from userInfo where id = 10

   修改：单条，多条修改

 1）单条：通过普通的sql语句进行修改，例如execute("update userInfo set userAge=100  where id = 1")

     通过占位符的方式进行传值：execute("update userInfo set userAge=%s  where id = %s",(100,2))

 2）多条：通过占位符，一次性操作多条：

    executemany("update userInfo set userAge=%s  where id = %s",[(100,3),(100,4),(100,5)])

11.事物性

   

def testTransaction():

    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")

    cursor = conn.cursor()

    try:

        sql1 = "insert into userInfo values(11,'测试人员8',31,'女','北京')"

        sql2 = "insert into userInfo values(10,'测试人员9',32,'女','北京')"

        result1 =  cursor.execute(sql1)

        result2 =  cursor.execute(sql2)

    except Exception,e:

        print str(e)

        conn.rollback()

    conn.commit()

    cursor.close()

    conn.close()

 

mysql事务自动提交的问题：

1.在mysql的系统配置文件my.ini文件中的[mysqld]下面加入autocommit =0

2.重启mysql服务，登录后事务的状态为off，通过show variables like '%commit%';查询如下：

  autocommit | OFF

3.如果事务还是没有生效,可能是表创建时所用的存储引擎出现问题，引擎如下：

  MyISAM效率更高，但不支持事务，不支持外键。----如果事务一开始被关闭，创建的表的引擎是这个，，不支持

                                             自动提交事务

  InnoDB效率略低，支持事务和外键。-----可以支持提交事务，需要把表的引擎修改过来，如下

4.修改表的存储引擎

show create table userInfo; 查看当前表所在的引擎：ENGINE=MyISAM DEFAULT CHARSET=utf8

ALTER TABLE userInfo ENGINE=InnoDB  修改当前表的引擎

# encoding=utf-8
import MySQLdb

#begin---建立连接(connection)-》游标-》操作数据（增/删/改查）-》获取结果-》
#  -》关闭游标-》关闭连接---end

def getConn_cursor():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    return conn,cursor;
#插入并查询所有结果
def insert():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    #例如：insert into userInfo values(1,'张三',20,'男','北京');
    #单条插入
    insertSql="insert into userInfo values(5,'测试人员2',25,'女','北京')"
    insertSql2="insert into userInfo values(%s,%s,%s,%s,%s)"
    # insertResult2 = cursor.execute(insertSql2,(7,'测试人员4',27,'女','北京'))
    #多条插入
    insertResult2 = cursor.executemany(insertSql2,[(8,'测试人员5',28,'女','北京'),(9,'测试人员6',29,'女','北京'),(10,'测试人员7',30,'女','北京')])
    print insertResult2
    conn.commit()
    cursor.close()
    conn.close()
def select():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    result =  cursor.execute("select * from userInfo")
    print "rownumber1",cursor.rownumber
    print cursor.fetchone()
    print "rownumber2",cursor.rownumber
    print cursor.fetchone()
    print "rownumber3",cursor.rownumber
    print cursor.fetchmany(2)
    print "rownumber4",cursor.rownumber
    print cursor.fetchall()
    print "rownumber5",cursor.rownumber
    print cursor.fetchone()
    print "rownumber6",cursor.rownumber
    print "result: ",result
    print cursor.fetchone()
    print "rownumber7",cursor.rownumber
    print "result: ",result

    cursor.close()
    conn.close()
def delete():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    result =  cursor.execute("delete  from userInfo where id = 8")
    print "result: ",result
    conn.commit()
    cursor.close()
    conn.close()
#修改
def update():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    result =  cursor.execute("update userInfo set userAge=100  where id = 1")
    sql = "update userInfo set userAge=%s  where id = %s"
    result =  cursor.execute(sql,(101,2))
    print "result: ",result
    results =  cursor.executemany(sql,[(100,3),(100,4),(100,5)])
    print "results: ",results
    conn.commit()
    cursor.close()
    conn.close()

def testTransaction():
    conn = MySQLdb.connect(host="127.0.0.1",port=3306,user="root",passwd="root",db="mydb",charset="utf8")
    cursor = conn.cursor()
    try:
        sql1 = "insert into userInfo values(13,'测试人员8',31,'女','北京')"
        sql2 = "insert into userInfo values(10,'测试人员9',32,'女','北京')"
        result1 =  cursor.execute(sql1)
        result2 =  cursor.execute(sql2)
    except Exception,e:
        print str(e)
        conn.rollback()
    conn.commit()
    cursor.close()
    conn.close()

if __name__ == '__main__':
    # insert()
    #select()
    # delete()
    # select()
    testTransaction()