python链接mysql以及mysql中对表修改的常用语法

MySQL是一个关系型数据库管理系统 ，其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。在使用过程中不总是和它打交道，导致使用时候都得查看具体的语法。下面为大家稍作总结一些常用到的（虽然常用的都背的溜溜的~）

在python3中使用mysql首先导入pymysql模块，没有这个模块的可以进行pip安装，要是mysql也没有的话，，参考http://www.cnblogs.com/xsmile/p/7753984.html进行安装。

进入mysql中，查看当前的含有的数据库都有哪些：

mysql -h localhost -u root -p    输入密码进入mysql环境。

(xsmile@xsmile:~$ mysql -u root -pmima -h ip -P 3306)  linux 系统下访问远程数据库

show databses;　　#显示当前数据库列表

show tables;　　#显示当前数据库中包含的所有表

create database db_name default character set utf8 collate utf8_general_ci;　　#创建数据库，字符集为utf-8的格式

alter table table_name default character set utf8; 　　# 修改现有表的编码集

show create table table_name;　　#查看表结构

desc table_name;　　#查看表结构

alter table old_name rename to new_name　　#修改数据表名

alter table table_name change old_column_name new_column_name new_type　　#修改列名

alter tale table_name modify column_name new_type　　#修改列的数据类型

alter table table_name add column_name datatype　　#插入列

alter rable table_name drop column column_name　　#删除列

alter table table_name modify column column_name int primary key auto_increment;　　#更改列为自增字段（前提是该字段为主键）

要在dos窗口操作数据库，先选择一个数据库，才能进行对其当中的表进行操作。即  use db_name;

我以test 数据库中 blog_test表操作为例：

mysql> use test;
Database changed
mysql> select * from blog_test;

之后屏幕输出：

+----------+-----------------+--------+
| username | email           | gender |
+----------+-----------------+--------+
| charles  | charles@163.com | male   |
| jerry    | jerry@qq.com    | female |
| xsmile   | xsmile@163.com  | male   |
+----------+-----------------+--------+
3 rows in set (0.03 sec)

当然重点在python中对mysql的操作，基本分为：

1、导入pymysql ；　　2、链接到某个数据库；　　3、创建游标；　　4、要操作的语法。

在数据库中创建表：

import pymysql　　#导入包
db=pymysql.connect(host='localhost',user='root',passwd='123456',db='test')　　#链接数据库
cursor=db.cursor()　　#创建游标，对表进行操作
cursor.execute("drop table if EXISTS blog_test")　　#如存在blog_test表，则删除
cursor.execute("create table blog_test( \
username char (20) ,\
email CHAR (20) NOT NULL ,\
gender CHAR (6) NOT NULL,\
PRIMARY key(username))charset=utf8")　　#charset=utf8 使得表支持中文，否则默认为Latin1
cursor.close()
db.commit()
db.close()

创建一个空表也没什么用啊，里面有内容才有价值，才有使用的意义啊。

数据库中插入数据。

cursor.execute("insert into blog_test(username,email,gender) values('xsmile','xsmile@163.com','male')")

很生动形象的语句，和mysql中语法简直一样。以后学到sqlalchemy 这个模块时就会方便很多了~，数据库中插入多条语句时用多条insert 语句。这里我们在代码中就有简洁的方法，对要插入的内容创建列表，就完成了多条插入。

ins="insert into blog_test VALUES (%s,%s,%s)"
datas=[ ('charles','charles@163.com','male'),('jerry','jerry@qq.com','female'),]
cursor.executemany(ins,datas)

插入这里用的是：cursor.executemany() ，不过有个小提示，下面有 db.commit() 才可以实现插入到表中。不然虽无错误，但插不到表中。

先进行查询看看表中数据

import pymysql　　#导入模块
db=pymysql.connect(host='localhost',user='root',passwd='123456',db='test')　　#链接到数据库
cursor=db.cursor()　　#创建游标对表进行相关操作
li=cursor.execute("select * from blog_test")　　#对表进行查询
print(li)
cursor.close()　　#关闭游标
db.close()　　#断开数据库

这时我们发现输出的结果是一个数字 3，什么情况，查看数据库表即blog_test看到当中有3条数据。原来如此，那我们想显示那3条数据怎么办呢？！

当然是循环了，依次对表中元组输出。

info=cursor.fetchmany(li)
for item in info:
    print(item)

还有cursor.fetchone() ,表示从中取一条数据。取其中元组时还有cursor.fetchall(), 不需要参数，取得的数据为游标当前位置到数据末尾的全部数据。在进行条件查询时可以加入where条件，语句和mysql中方法是一样的。

上面提到的有查询和插入，接下来看看更改操作。

cursor.execute("update blog_test set username='sofia' where email='xsmile@163.com'")

注意在查询字段中引号的使用，不能缺少。

删除操作的话想必大家都知道了，把mysql语句放在cursor.execute()中，即可完成相关操作。那我们把刚才更改的那行数据进行删除。

cursor.execute("delete from blog_test where username='sofia'")

之后再查询blog_test表，名为sofia的数据信息就删除了。当然要删除整个表的话，就使用drop语句了，三思后行这个操作，不然你就是删库到跑路的那个人~

删除以及开启事务及回滚

在进行插入、更改，删除（delete）操作时，可以事先开启事务，这样当出错或返回时只需rollback回滚下就恢复为原来的数据了（在pycharme中默认开启事务，但最后还有commit(),这样程序执行后，也是无法弥补的）。例如：

mysql> select * from b;　　#查看b表数据
+------+
| b    |
+------+
|    4 |
|    5 |
|    6 |
|    7 |
+------+
4 rows in set (0.00 sec)

mysql> begin;　　#开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> delete from b where b=5;　　#把为5的元组删除
Query OK, 1 row affected (0.05 sec)

mysql> select * from b;
+------+
| b    |
+------+
|    4 |
|    6 |
|    7 |
+------+
3 rows in set (0.00 sec)

mysql> rollback;　　#回滚
Query OK, 0 rows affected (0.11 sec)

mysql> select * from b;
+------+
| b    |
+------+
|    4 |
|    5 |
|    6 |
|    7 |
+------+
4 rows in set (0.00 sec)

但是drop（将表格直接删除，没有办法找回）、以及truncate（删除表中的所有数据，不能与where一起使用）删除时回滚也挽回不了删除的数据。

 

原创不易，尊重版权。转载请注明出处：http://www.cnblogs.com/xsmile/