python关于mysql的API--pymysql模块
pymsql是Python中操作MySQL的模块,其使用方法和py2的MySQLdb几乎相同。 模块安装 pip?install?pymysql 执行sql语句 import?pymysql #添加数据 conn?=?pymysql.connect(host='127.0.0.1',?port=3306,?user='root',?passwd='',?db='yyy') cursor?=?conn.cursor() #?sql?=?"""CREATE?TABLE?EMPLOYEE?( #??????????FIRST_NAME??CHAR(20)?NOT?NULL,#??????????LAST_NAME??CHAR(20),#??????????AGE?INT,#??????????SEX?CHAR(1),#??????????INCOME?FLOAT?)""" #? #?cursor.execute(sql) #row_affected?=?cursor.execute("create?table?t1(id?INT?,name?VARCHAR(20))") #row_affected=cursor.execute("INSERT?INTO?t1(id,name)?values?(1,'alvin'),(2,'xialv')") #cursor.execute("update?t1?set?name?=?'silv2'?where?id=2") #查询数据 row_affected=cursor.execute("select?*?from?t1") one=cursor.fetchone() #?many=cursor.fetchmany(2) #?all=cursor.fetchall() #scroll #cursor.scroll(-1,mode='relative')??#?相对当前位置移动 #cursor.scroll(2,mode='absolute')?#?相对绝对位置移动 #更改获取数据结果的数据类型,默认是元组,可以改为字典等:conn.cursor(cursor=pymysql.cursors.DictCursor) conn.commit() cursor.close() conn.close() 事务 事务指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部不成功。 数据库开启事务命令
?UPDATE account set balance=balance-5000 WHERE name="lily"; ?UPDATE account set balance=balance+5000 WHERE name="tom"; create?table?test2(id?int?PRIMARY?KEY?auto_increment,name?VARCHAR(20))?engine=innodb; INSERT?INTO?test2(name)?VALUE?("alvin"),??????????????????????????????("lily"),??????????????????????????????("tom"); start?transaction; delete?from?test2;???? select?*?from?test2;??--?表被删除 rollback;????????--回滚 select?*?from?test2; start?transaction; insert?into?test2?(name)values('silv'); select?*?from?test2; commit; --?保留点 start?transaction; insert?into?test2?(name)values('wu'); savepoint?insert_wu;????--保留点 select?*?from?test2; delete?from?test2?where?id=4; savepoint?delete1;???? select?*?from?test2; delete?from?test2?where?id=1; savepoint?delete2; select?*?from?test2; rollback?to?delete1; select?*?from?test2; python中调用数据库启动事务的方式 import?pymysql #添加数据 conn?=?pymysql.connect(host='127.0.0.1',?db='yyy') cursor?=?conn.cursor() try: ????insertSQL0="INSERT?INTO?ACCOUNT2?(name,balance)?VALUES?('oldboy',4000)" ????insertSQL1="UPDATE?account2?set?balance=balance-30?WHERE?name='lily'" ????insertSQL2="UPDATE?account2?set?balance=balance+30?WHERE?name='tom'" ????cursor?=?conn.cursor() ????cursor.execute(insertSQL0) ????conn.commit() ????cursor.execute(insertSQL1) ????raise?Exception ????cursor.execute(insertSQL2) ????cursor.close() ????conn.commit() except?Exception?as?e: ????conn.rollback() ????conn.commit() cursor.close() conn.close() 索引 一 索引简介? 索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。 索引优化应该是对查询性能优化最有效的手段了。 索引能够轻易将查询性能提高好几个数量级。 索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。 索引特点:创建与维护索引会消耗很多时间与磁盘空间,但查询速度大大提高! 二 索引语法 --创建表时 --语法: ????CREATE?TABLE?表名?( ????????????????字段名1??数据类型?[完整性约束条件…],????????????????字段名2??数据类型?[完整性约束条件…],????????????????[UNIQUE?|?FULLTEXT?|?SPATIAL?]???INDEX?|?KEY ????????????????[索引名]??(字段名[(长度)]??[ASC?|DESC]) ????????????????); -------------------------------- --创建普通索引示例: ????CREATE?TABLE?emp1?( ????????id?INT,????????name?VARCHAR(30)?,????????resume?VARCHAR(50),????????INDEX?index_emp_name?(name) ????--KEY?index_dept_name?(dept_name) ????????); --创建唯一索引示例: ????CREATE?TABLE?emp2?( ????????id?INT,????????bank_num?CHAR(18)?UNIQUE?,????????UNIQUE?INDEX?index_emp_name?(name) ????????); --创建全文索引示例: ????CREATE?TABLE?emp3?( ????????id?INT,????????FULLTEXT?INDEX?index_resume?(resume) ????????); --创建多列索引示例: ????CREATE?TABLE?emp4?( ????????id?INT,????????INDEX?index_name_resume?(name,?resume) ????????); --------------------------------- ---添加索引 ????---CREATE在已存在的表上创建索引 ??????CREATE??[UNIQUE?|?FULLTEXT?|?SPATIAL?]??INDEX??索引名 ??????????????ON?表名?(字段名[(长度)]??[ASC?|DESC])?; ???? ????---ALTER?TABLE在已存在的表上创建索引 ???? ??????ALTER?TABLE?表名?ADD??[UNIQUE?|?FULLTEXT?|?SPATIAL?]?INDEX ????????????????????索引名?(字段名[(长度)]??[ASC?|DESC])?; ???? ???? ???? ?????CREATE?INDEX?index_emp_name?on?emp1(name); ?????ALTER?TABLE?emp2?ADD?UNIQUE?INDEX?index_bank_num(band_num); --?删除索引 ???? ????语法:DROP?INDEX?索引名?on?表名 ???? ????DROP?INDEX?index_emp_name?on?emp1; ????DROP?INDEX?bank_num?on?emp2; 三 索引测试实验 --创建表 create?table?Indexdb.t1(id?int,name?varchar(20)); --存储过程 delimiter?$$ create?procedure?autoinsert()? BEGIN declare?i?int?default?1; while(i<500000)do insert?into?Indexdb.t1?values(i,'yuan'); set?i=i+1; end?while; END$$ delimiter?; --调用函数 call?autoinsert(); --?花费时间比较: --?创建索引前 ???select?*?from?Indexdb.t1?where?id=300000;--0.32s --?添加索引? ???create?index?index_id?on?Indexdb.t1(id); --?创建索引后 ???select?*?from?Indexdb.t1?where?id=300000;--0.00s (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |