pymysql 和 mysql 语法整理

目录

1 连接数据库 2 创建表 3 插入数据 4 更新数据 5 查询数据 6 删除数据

---

1.连接数据库

• connect() 方法声明一个MySQL连接对象db,此时需要传入MySQL运行的host（即IP）。由于MySQL在本地运行，所以传入的是localhost。如果MySQL在远程运行，则传入其公网IP地址。后续的参数user即用户名，password即密码，port即端口（默认3306）
• cursor() 方法获得MySQL的操作游标，利用游标来执行SQL语句。
• execute() 方法将字符串内容当做命令来执行，作用是动态创建和执行SQL语句。
• fetchone() 方法获得第一条数据

连接数据库 假设MySQL运行在本地，用户名为root，密码是root，运行端口为3306，用PyMySQL连接MySQL，打印MySQL数据库的版本信息，然后创建一个新的数据库test

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123...', port=3306)
tt = db.cursor()

tt.execute('select version()')                          # 查看mysql版本
data = tt.fetchone()
print('MySql版本:', data)

tt.execute('create database cs_4 charset=utf8')         # 创建数据库,默认编码utf8

tt.close()                                              # 关闭游标的使用
db.close()                                              # 关闭数据库

2 创建表

创建数据库后，在数据库中创建数据表连接时需要额外指定一个参数db 新创建一个数据表User结构如下，执行创建表的的SQL语句即可

Field	Type	Null	Key	Default	Extra
id	int unsigned	NO	PRI	NULL	auto_increment
姓名	varchar(30)	YES		NULL
年龄	tinyint unsigned	YES		0
身高	decimal(5,2)	YES		NULL
性别	enum(‘男’,‘女’,‘保密’)	YES		保密
班级	int unsigned	YES		NULL

创建该表的代码如下：

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')  # 参数db为指定数据库
tt = db.cursor()

# if not exists 没有该表则创建该表 unsigned   既为非负数，用此类型可以增加数据长度!
sql = '''create table if not exists 表名3(
        id int unsigned not null auto_increment primary key,
        姓名 varchar(30), 
        年龄 tinyint unsigned default 0, 
        身高 decimal(5, 2),
        性别 enum("男", "女","保密") default "保密",
        班级 int unsigned )
        '''
tt.execute(sql)                                      # 执行sql命令语句

tt.close()                                           # 关闭游标的使用
db.close()                                           # 关闭数据库

3 增：插入数据

向数据库中插入一个用户信息，编号为‘1’，名字为‘林先生‘，年龄20，代码如下：

• commit()：需要执行db对象的commit()方法才可实现数据插入，这个方法才是真正将SQL语句提交到数据库执行的方法。对于数据的增删改查操作，都需要调用该方法才能生效。
• rollback()：如果执行失败，调用rollback()方法执行数据回滚，相当于什么都没发生过。

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')
tt = db.cursor()

name = '王同学'
age = 28
sg = 170
xb = "女"
bj = 2

sql = 'insert into 表名1(姓名,年龄,身高,性别,班级) values (%s,%s,%s,%s,%s)'
try:
    if tt.execute(sql, (name, age, sg, xb, bj)):
        print('插入数据成功')
        db.commit()             # 数据真正提交
except:
    print('插入数据失败')
    db.rollback()               # 回滚
tt.close()                      # 关闭游标的使用
db.close()                      # 关闭数据库

上面的数据插入的操作是通过构造SQL语句实现，但是有一个极其不方便的地方，如果突然增加了性别字段gender，此时SQL语句就需要改成：

INSERT INTO user (id, name,age, gender) values (%s, %s, %s, %s)

相应的元组参数则需要改成：(id, name, age, gender)

这显然有点麻烦，我们要达到的效果是插入数据方法无需改动，做成一个通用的方法，只需要传入一个动态变化的字典就好了。

z_dic = {
    '姓名': '张三丰',
    '年龄': '78',
    '身高': 169,
    '性别': '男',
    '班级': 0
}

增：重点综合写法：

然后SQL语句会根据字典动态创造，元组也动态创造，实现通用的插入方法，这里我们修改一下插入方法。

import pymysql

db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')
tt = db.cursor()
z_dic = {
    '姓名': '张三丰',
    '年龄': '78',
    '身高': 169,
    '性别': '男',
    '班级': 0
}
table = '表名1'                                     # 表名
keys = ', '.join(z_dic.keys())                     # 固定写法：获取data字典索引值
values = ', '.join(['%s'] * len(z_dic))            # 固定写法：获取data字典sky值
sql = 'insert into {}({}) VALUES ({})'.format(table, keys, values)

try:
    if tt.execute(sql, tuple(z_dic.values())):
        print('插入数据成功')
        db.commit()                               # 提交数据
except:
    print('插入数据失败')
    db.rollback()

tt.close()                                       # 关闭游标的使用
db.close()                                       # 关闭数据库

这里要传入的数据data是一个字典变量，变量table指表名，接下来构建一个动态的SQL语句。

首先，需要插入的字段是id, name, age.这里将data的键名取出，然后用逗号分隔，所以keys=’, ‘.join(data.keys())的结果就是keys=‘id, name, age’ 。然后构建多个占位符%s，有几个字段构建几个占位符。values=’, ‘.join([’%s’]*len(data))结果等于values=’%s, %s, %s’。最后利用format()方法将表名、字段名和占位符构造出来。最终的SQL语句如下：

INSERT INTO user(id,name,age) values (%s,%s,%s)

execute() 第一个参数传入SQL变量，第二个参数传入data的键值构造的元组

4 改：更新数据

数据更新操作实际上也是执行SQL语句，这里同样用占位符的方法构造SQL，然后执行execute()方法，插入元组形式的参数，同样执行commit()方法执行操作。

• 命令语法：UPDATE 表名 SET 姓名=‘刘阿三’ WHERE id=3;
• <表名>：用于指定要更新的表名称。
• SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
• WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
• ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
• LIMIT 子句：可选项。用于限定被修改的行数。

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')
tt = db.cursor()

sql = '''update 表名1 set 年龄 = 59 where id = 3 '''           # 更新单项数据
try:
    if tt.execute(sql):
        print('更新数据成功')
        db.commit()
except:
    print('更新数据失败')
    db.rollback()
tt.close()                                                    # 关闭游标的使用
db.close()                                                    # 关闭数据库

改：实际操作推荐写法

在实际操作中，有时可能会出现重复的数据，如果出现了，我们希望更新数据而不是重复保存一次。所以这里可以再实现一种去重的方法，如果数据存在，则更新数据；如果数据不存在，则插入数据。另外，这种做法支持灵活的字典传值。

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')
tt = db.cursor()

data = {
    'id': '3',
    '姓名': '张小姐',
    '年龄': 32,
    '身高': 168,
    '性别': '女',
    '班级': 10,
}

table = '表名1'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'insert into {}({}) values ({}) ON DUPLICATE KEY UPDATE'.format(table, keys, values)
update = ','.join([" {} = %s".format(key) for key in data])
sql += update                                         # 固定写法
print(sql)
print(tuple(data.values())*2)
try:
    if tt.execute(sql, tuple(data.values())*2):      # 固定写法，2处需插入%s，所以*2
        print('更新数据成功')
        db.commit()
except:
    print('更新数据失败')
    db.rollback()
tt.close()                                          # 关闭游标的使用
db.close()                                          # 关闭数据库

这里构建的SQL语句其实是插入语句，但是在后面加了ON DUPLICATE KEY UPDATE。这行代码的意思是如果主键已经存在，就执行更新操作。比如我们传入的id依然是‘1’，但是年龄有变化,由25变成了22，此时这条数据不会被插入，而是更新id为‘1’的数据。

完整的SQL语句如下

INSERT INTO user (id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s

这里有六个%s占位符。所以在后面的execute()方法的第二个参数就需要乘以2变成原来的2倍。这样我们就实现主键不存在便插入数据，存在则更新数据的功能了。

5 查：查询数据

查询会用到SELECT语句，这里我们构建一个SQL语句，将年龄20及以上的用户查询出来。源代码如下

• rowcount属性获取查询结果的条数
• fetchone()方法获取结果的第一条数据，返回结果是元组形式
• fetchall()方法可以得到结果的所有数据，它是一个二重元组

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123...', port=3306, db='cs_3')
tt = db.cursor()

sql = 'select * from 表名1 where 年龄 >= 20'       # 查询年龄大于等于20的数据
try:
    tt.execute(sql)                              # 查询到条数
    print('合计:', tt.rowcount, '条')
    # one = tt.fetchone()                        # 获得第一条数据
    # print('获得第一条数据:', one)
    result = tt.fetchall()                       # 获得所有符合要求的数据
    print('获得所有的数据:',result)
    for row in result:                           # 循环输出result的值
        print(row)
except:
    print('查收数据失败')

tt.close()                      # 关闭游标的使用
db.close()                      # 关闭数据库

这里有一个问题：fetchall() 方法显示的是2条数据而不是3条，fetchall() 方法不是获取所有的数据吗？为什么会这样？原因是它的内部实现有一个偏移指针来指向查询结果，最开始偏移指针指向第一条数据，取一次后，指针偏移到下一条数据，这样再取的话，就会取到下一条数据了。我们最初调用了 一次fetchone() 方法，这样结果的偏移指针就指向下一条数据，fetchall() 方法返回的是偏移指针指向的数据一直到结束的所有数据，所以该方法在这里获取的结果就只剩2个。

查：推荐写法

我们还可以用while循环加fetchone()方法来获取所有数据，而不是用fetchall()全部一起获取出来。fetchall()方法会将结果以元组形式全部返回，如果数据量很大，那么占用的开销会非常高。因此，推荐如下方法来逐条取数据,每循环一次，指针偏移一条数据

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='cs_2')
cursor = db.cursor()
sql = 'SELECT * FROM 表名 WHERE 年龄 >= 30'
try:
    cursor.execute(sql)
    print('合计数据:', cursor.rowcount)
    row = cursor.fetchone()
    while row:                                     # 循环输出每一条数据
        print('数据:', row)
        row = cursor.fetchone()
except:
    print('查询数据失败')

cursor.close()                  # 关闭游标的使用
db.close()                      # 关闭数据库

6 删：删除数据

删除操作相对简单，使用DELETE语句即可，需要指定要删除的目标表名和删除条件，而且仍然需要使用db对象的commit() 方法才能生效。删除操作不可逆，操作需谨慎。

删除年龄大于90的用户数据，代码如下：

import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='cs_2')
cursor = db.cursor()

table = '表名'
condition = '年龄 > 90'
sql = 'DELETE FROM {} WHERE {}'.format(table, condition)     # 删除数据
try:
    if cursor.execute(sql):
        print('删除数据成功')
        db.commit()
except:
    print('删除数据失败')
    db.rollback()

cursor.close()                  # 关闭游标的使用
db.close()                      # 关闭数据库

7.防止pymysql注入

SQL 注入是非常常见的一种网络攻击方式，主要是通过参数来让 mysql 执行 sql 语句时进行预期之外的操作。
例如，下面这段代码通过获取用户信息来校验用户权限：

import pymysql

sql = 'SELECT count(*) as count FROM user WHERE id = ' + str(input['id']) + ' AND password = "' + input['password'] + '"'
cursor = dbclient.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
count = cursor.fetchone()
if count is not None and count['count'] > 0:
    print('登陆成功')

但是，如果传入参数是：

input['id'] = '2 or 1=1'

你会发现，用户能够直接登录到系统中，因为原本 sql 语句的判断条件被 or 短路成为了永远正确的语句。
这里仅仅是举一个例子，事实上，sql 注入的方式还有很多种，这里不深入介绍了。
总之，只要是通过用户输入数据来拼接 sql 语句，就必须在第一时间考虑如何避免 SQL 注入问题。
那么，如何防止 SQL 注入呢？

如何预防 SQL 注入 – pymysql 参数化语句
pymysql 的 execute 支持参数化 sql，通过占位符 %s 配合参数就可以实现 sql 注入问题的避免。

import pymysql

sql = 'SELECT count(*) as count FROM user WHERE id = %s AND password = %s'
valus = [input['id'], input['password']]
cursor = dbclient.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, values)
count = cursor.fetchone()
if count is not None and count['count'] > 0:
    print('登陆成功')

这样参数化的方式，让 mysql 通过预处理的方式避免了 sql 注入的存在。
需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用占位符。
同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。

更多详细介绍，防止mysql注入，查看链接

8.mysql命令语句整理

1. 创建表
   命令：create table <表名> ( <字段名1> <类型1> [,…<字段名n> <类型n>]);
   例如，建立一个名为MyClass的表

mysql> create table MyClass(
> id int(4) not null primary key auto_increment,
> name char(20) not null,
> sex int(4) not null default '0',
> degree double(16,2));

2. 获取表结构
   命令： desc 表名，或者show columns from 表名

mysql> desc MyClass;
mysql> show columns from MyClass;

使用MySQL数据库desc 表名时，我们看到Key那一栏，可能会有4种值，即’ '，‘PRI’，‘UNI’，‘MUL’。
(1).如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列；
(2).如果Key是PRI, 那么该列是主键的组成部分；
(3).如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)；
(4).如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。
如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI,又是UNI，那么"desc 表名"的时候，显示的Key值按照优先级来显，PRI->UNI->MUL。那么此时，显示PRI。
一个唯一性索引列可以显示为PRI,并且该列不能含有空值，同时该表没有主键。
一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引，因为虽然索引的多列组合是唯一的，比如ID+NAME是唯一的，但是没一个单独的列依然可以有重复的值，只要ID+NAME是唯一的即可。

3. 删除表
   命令：drop table <表名>
   例如：删除表名为 MyClass 的表

mysql> drop table MyClass;

DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！
注意：对于一个带分区的表，DROP TABLE会永久性地取消表定义，取消各分区，并取消储存在这些分区中的所有数据。DROP TABLE还会取消与被取消

的表有关联的分区定义（.par）文件。
对与不存在的表，使用IF EXISTS用于防止错误发生。当使用IF EXISTS时，对于每个不存在的表，会生成一个NOTE。
RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。

4. 向表插入数据
   命令：insert into <表名> [( <字段名1>[,…<字段名n > ])] values ( 值1 )[, ( 值n )]
   例如：往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99， 编号为3 的名为

Wang 的成绩为96.5。

mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

注意：insert into每次只能向表中插入一条记录。

5. 查询表
   (1)、查询所有行
   命令： select <字段1，字段2，…> from < 表名 > where < 表达式 >
   例如：查看表 MyClass 中所有数据

mysql> select * from MyClass;

(2）、查询前几行数据
例如：查看表 MyClass 中前2行数据

mysql> select * from MyClass order by id limit 0,2;

select一般配合where使用，以查询更精确更复杂的数据。

6. 删除表
   命令：delete from 表名 where 表达式
   例如：删除表 MyClass中编号为1 的记录

mysql> delete from MyClass where id=1;

7. 修改表中的数据
   语法：update 表名 set 字段=新值,… where 条件
   mysql> update MyClass set name=‘Mary’ where id=1;

例子1：单表的MySQL UPDATE语句：

复制代码 代码如下:

 UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

例子2：多表的UPDATE语句：
复制代码 代码如下:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

8. 增加表字段

命令：alter table 表名 add字段 类型 其他;
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0
mysql> alter table MyClass add passtest int(4) default '0'[/code]
加索引：mysql> alter table 表名 add index 索引名 (字段名1[，字段名2 …]);
例子： mysql> alter table employee add index emp_name (name);
加主关键字的索引：mysql> alter table 表名 add primary key (字段名);
例子： mysql> alter table employee add primary key(id);
加唯一限制条件的索引：mysql> alter table 表名 add unique 索引名 (字段名);
例子： mysql> alter table employee add unique emp_name2(cardnumber);
删除某个索引：mysql> alter table 表名 drop index 索引名;
例子： mysql>alter table employee drop index emp_name;
增加字段：mysql> ALTER TABLE table_name ADD field_name field_type;
修改原字段名称及类型：mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
修改字段类型：mysql>ALTER TABLE table_name MODIFY colum_name field_type new_type
删除字段：MySQL ALTER TABLE table_name DROP field_name;

9. 修改表名
   命令：rename table 原表名 to 新表名;
   例如：在表MyClass名字更改为YouClass

复制代码 代码如下:

mysql> rename table MyClass to YouClass;

当你执行 RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。
如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。
RENAME TABLE 在 MySQL 3.23.23 中被加入。

10. 备份数据
    命令在DOS的[url=file://\mysql\bin]\mysql\bin[/url]目录下执行
    (1).导出整个数据库
    导出文件默认是存在mysql\bin目录下

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u user_name -p123456 database_name > outfile_name.sql

(2).导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql

(3).导出一个数据库结构

mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql
-d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table

(4).带语言参数导出

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql

例如，将aaa库备份到文件back_aaa中：

复制代码 代码如下:
[root@test1 root]# cd　/home/data/mysql
[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa

11. 完整案例

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
 id int(3) auto_increment not null primary key,
 name char(10) not null,
 address varchar(50) default ''深圳'',
 year date
); //建表结束
 
//以下为插入字段
insert into teacher values('''',''glchengang'',''深圳一中'',''1976-10-10'');
insert into teacher values('''',''jack'',''深圳一中'',''1975-12-23'');

注：在建表中
(1)、将ID设为长度为3的数字字段:int(3)；并让它每个记录自动加一:auto_increment；并不能为空:not null；而且让他成为主字段primary key。
(2)、将NAME设为长度为10的字符字段
(3)、将ADDRESS设为长度50的字符字段，而且缺省值为深圳。

如果你在mysql提示符键入上面的命令也可以，但不方便调试。
(1)、你可以将以上命令原样写入一个文本文件中，假设为school.sql，然后复制到c:\下，并在DOS状态进入目录[url=file://\mysql\bin]\

\mysql\bin[/url]，然后键入以下命令：mysql -uroot -p密码 < c:\school.sql
如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你只要将//的注释去掉即可使用）。
(2)、或者进入命令行后使用 mysql> source c:\school.sql; 也可以将school.sql文件导入数据库中。

以上就是完整版的Mysql命令大全，希望对大家熟练使用Mysql命令有所帮助。