day60 pymysql

预知扩展内容,详见地址如下(关于数据库备份和恢复)

   http://www.cnblogs.com/linhaifeng/articles/7525619.html

我们一般写程序都是在py文件里面,那么当我们需要跟数据库连接的时候,如果在另外开启一个数据库系统的话就会很费时费力,这个时候我们的pycharm就给我们提供了这种便利,就是使用pycharm来连接我们的数据库系统,然后进行操作,直接使用pycharm一个程序就可以了,方便快捷,我们的一切行为以提高工作效率为宗旨.所以要善用工具

 

我们先要在我们的pycharm里面下载一个pycharm模块,然后再py文件的顶部引用这个模块就可以投入使用了,[这里需要注意的是我们在pycharm3版本里面使用的是pymycql这个模块,

而在pycharm2版本里面使用的是mysqldb这个模块,这是e2和3的区别]

 我们先来看一下在pycharm里面如何来调出mysql的窗口来

如图所示:

 

在上图中我们可以看到databases这个按钮,这里就是我们的mysql数据库窗口,点进去就可以看到我们之前在mysql的辅助工具里面的看到的类似的页面,然后就能根据提示操作了,

 

在这里操作完之后需要根据提示有一个用户名和密码的输入框,需要授权之后才可以访问数据库.

 

 

这里点击之后我们就可以调出执行面板

执行面板就长这样:

 

然后就可以根据需求我们把库/表格进行新建,修改,查询等操作建立或者修改完成后记得提交,在这里进行提交操作:

 

 

 

接下来就是一些python和mysql相结合的时候我们需要注意的一些语法问题,

然后 就是对数据库进行操作

首先是引入模块,import pymysql

然后就是建立连接,就像我们在网络编程里面服务端和客户端建立的连接一样的,需要绑定IP和端口,在这里我们需要绑定的不仅仅是IP和端口这两项,还有其他的参数都需要绑定,

conn=pymysql.connect(host="localhost",database="book_list",user="root",password="123",charset="utf8")  我们的端口是默认的3306,一般不写出来,可以写出来,

我们这里的conn是固定的变量,=是赋值,没什么好难理解的,

pymysql是模块,我们引入的模块,必须要写上,.connect是连接,也是固定的,就像客户端和服务端建立连接的时候一样用.connect来操作,然后就是括号里面的参数了,

host是IP地址,我们使用自己本地的就直接用localhost,

database是数据库的名字,

user是授权用户账户名

password是授权用户的密码

charset是字符编码,我们一般都是设定成为utf8[这一句是必须要加上的,否则取出来的sql语句里面如果有中文的话会出现乱码,这个参数是必须要加上的]

我们在mysql里面使用数据库的时候会有一个光标一直在闪烁提示我们输入命令,然后我们输入命令之后就可以得出结果,所以我们在python里面连接上数据库之后还需要获取这个光标然后我们就可以输入sql命令了

caa = conn.cursor() 这里我们使用的从你这个变量然后调用cursor这个方法就能够获取到这个光标,而=右边这个变量名字是随意起的,改成abc=conn.cursor()也是没有问题的

 

紧接着我们就要开始着手写sql语句了,

sql = "show databases;"

ret = caa.execute(sql)   我们在这里执行了上面的sql语句,要用到执行的语法---->execute()括号里面是需要执行的提前存好的sql语句赋值的变量,执行前需要使用到光标获取所赋值的变量

caa.close()  我们需要把光标关闭

conn.close()  然后再关闭掉连接 [就像我们在客户端和服务端需要关闭的网络连接是一样的]

print(ret)  这里我们打印出来最终的结果

 

接下来就是数据库的增删改查操作了,都是比较简单基础的东西,我们之前的mysql里面都讲过了,只是牵扯到了一些

python里面的语法,写的时候有些格式的问题,但是那些都比较简单,然后我们写几个例子也就记住了.基本套路就是上面的那些,

 

增:

按照上面的套路来,我们举一个增加内容的例子,

# 导入模块
import pymysql
# 连接database
conn = pymysql.connect(host="IP地址",user="用户名",password="密码",database="库名",charset="utf8")
# 得到一个可以执行的sql语句的光标对象
cu = conn.cursor()
# 定义要执行的sql语句
sql = "insert into userinfo(name,age) values (%s, %s);''
username = "alex"
age = 18
# 执行sql语句
cu.execute(sql, [username, age])
# 提交事物
conn.conmit()
cu.close()
conn.close()





数据回滚事件
导入模块
import pymysql
# 连接database
conn = pymysql.connect(host="ip地址", user="用户名", password="密码", database="库名",charset="utf8")
# 得到一个可以执行sql语句的光标对象
cur = conn.cursor()
sql = "insert into user158(name, age) values (%s, %s);"
username = "alex"
age = 90
try:
    # 执行sql语句
    cur.execute(sql, [username, age])
    # 提交事物  这里跟我们mysql里面学过的事物是一样的,sql没有问题就# 执行提交操作
    conn.commit()
except Exception as e:
    # 有异常, 回滚事物,如果有异常的话就直接回滚了,不会提交事物
    conn.rollback()
cur.close()
conn.close()

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host="localhost", database="book_list", user="root", password="123", charset="utf8")
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INT[]O userinfo(pwd, name) VALUES (%s, %s);"  # 这里我们设定一个不成立的sql语句,所以我们会捕捉这里的异常然后回滚它,如果设定一个成立的sql,那么我们就可以提交它
pwd = "Alex"
name = "alex"
try:
    # 执行SQL语句
    cursor.execute(sql, [pwd, name])
    # 提交事务
    conn.commit()
    # 提交之后，获取刚插入的数据的ID
    last_id = cursor.lastrowid
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()
# print(last_id)


批量执行操作:

批量执行

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"  
data = [("Alex", 18), ("Egon", 20), ("Yuan", 21)]  # 我们在这里传入多个数据,用元祖或者列表的形式
try:
    # 批量执行多条插入SQL语句
    cursor.executemany(sql, data)
    # 提交事务
    conn.commit()
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()

 

 删:

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host="localhost", database="book_list", user="root", password="123", charset="utf8")
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# sql = "INSERT INT[]O userinfo(pwd, name) VALUES (%s, %s);"
# pwd = "Alex"
# name = "alex"
sql = "delete from userinfo where id=%s;"
try:
    # 执行SQL语句
    cursor.execute(sql, [1])
    # 提交事务
    conn.commit()
    # 提交之后，获取刚插入的数据的ID
except Exception as e:
    # 有异常，回滚事务
    conn.rollback()
cursor.close()
conn.close()


改:

# 导入pymysql模块
import pymysql
# 连接database
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 修改数据的SQL语句
sql = "UPDATE userinfo SET age=%s WHERE name=%s;"
username = "Alex"
age = 80
try:
# 执行SQL语句
cursor.execute(sql, [age, username])
# 提交事务

conn.commit()
except Exception as e:
# 有异常，回滚事务
conn.rollback()
cursor.close()
conn.close()

 

查:

import pymysql
conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”)
cursor = conn.cursor()
sql = "SELECT id,name,age from USER1 WHERE id=1;"
cursor .execute(sql)
ret = cursor.fetchone()  # 获取单挑数据
# ret = cursor.fetchall() # 获取所有数据,得到的是一个元祖
# ret = cursor.fetchmany(2) # 括号里面是自定义的数字,指定获取几条数据
cursor.close()
conn.close()
print(ret)

 

 

光标按照绝对/相对位置移动

import pymysql

# 连接
conn = pymysql.connect(host="localhost", user="root", password="root1234", database="s8", charset="utf8")  # 没有-
# 获取光标
cursor = conn.cursor()
# 写sql语句
sql = "select * from userinfo;"

# 执行SQL语句
ret = cursor.execute(sql)
print("-->",ret)
# 一次取一条
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchone())
# 一次取所有
# print(cursor.fetchall())
# 一起取三条
# print(cursor.fetchmany(3))

# 进阶用法
print(cursor.fetchone())
print(cursor.fetchall())
print(cursor.fetchone())
# 移动取数据的光标
cursor.scroll(-2)  # 默认是相对移动
print(cursor.fetchone())
# 按照绝对位置去移动
cursor.scroll(4, mode="absolute")
print(cursor.fetchone())
cursor.close()
conn.close()

 按照绝对位置移动是没有什么好理解的,就是从第一条数据开始作为起点,在它的基础上进行移动

 

而我们用得最多的不是绝对位置,而是相对位置

这里是一些例子,可以便于我们理解:

import pymysql
conn = pymysql.connect(
    host="localhost",
    user="root",
    database="book_list",
    password="123",
    charset="utf8"
)

abc = conn.cursor()

sql = "select id, pwd, name from userinfo;"

abc.execute(sql)

# print("---->", ret)
# abc.scroll(0)  # 我们这里的索引值是从0开始的,
# 也就是说我们的第一行数据的索引值是0,第二行的索引值是1,而第三行的索引值是2依次类推
# print(abc.fetchone())
# print(abc.fetchone())
# print(abc.fetchmany(2))
# print(abc.fetchall())
# abc.scroll(4)  # 这里是相对于我们的光标目前所在的位置进行移动的,
# print(abc.fetchone())
# print(abc.fetchmany(2))
# print(abc.fetchall())
# abc.scroll(-4)  # 我们的程序走到这一步的时候我们已经把所有的数据都取出来了,
# 这个时候把光标移动到当前位置(也就是末尾的位置的时候)的相对位置,
# 就是相当于在末尾的位置的基础上-4的位置,也就是从下往上移动了4个格,就到了倒数第4个位置上面,然后就可以接着去取值了
# print(abc.fetchone())
# print(abc.fetchall())
abc.scroll(1)
print(abc.fetchone())
abc.scroll(2)
print(abc.fetchone())
abc.scroll(-3)
print(abc.fetchone())
abc.scroll(2)
print(abc.fetchone())
abc.close()
conn.close()

我们先定义一个scroll(2)

然后fetchone()得到一个数据,看看它所在的位置,假设它是a

再定义一个scroll(3)

fetchone()得到一个数据,这个时候就可以得出对比的结果了,我们要这样去举例子才能明白怎么回事,

这里的结果假设它是b,就是我们在上面的scroll(2),fetchone()得到的a的基础上再进行移动,移动了3个数据的位置,才得到这里的b

这里就是相对移动,我们不传参数的话就是默认相对位置移动,

总结,我们的相对位置移动这样更加灵活,比如我们取数据的时候取到了第4条,然后想返回去取第2条的时候就把光标-2移动到第二条就可以了,这样更加灵活

 

获取插入的最后一条数据的自增id使用

lastrowid方法
如下例:

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()

sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()

cursor.close()
conn.close()

 

pymysql的权限管理操作:

#授权表
user #该表放行的权限，针对：所有数据，所有库下所有表，以及表下的所有字段
db #该表放行的权限，针对：某一数据库，该数据库下的所有表，以及表下的所有字段
tables_priv #该表放行的权限。针对：某一张表，以及该表下的所有字段
columns_priv #该表放行的权限，针对：某一个字段

#按图解释：
user：放行db1，db2及其包含的所有
db：放行db1，及其db1包含的所有
tables_priv:放行db1.table1，及其该表包含的所有
columns_prive:放行db1.table1.column1，只放行该字段

 

#创建用户
create user 'egon'@'1.1.1.1' identified by '123';
create user 'egon'@'192.168.1.%' identified by '123';
create user 'egon'@'%' identified by '123';


#授权：对文件夹，对文件，对文件某一字段的权限
查看帮助：help grant
常用权限有：select,update,alter,delete
all可以代表除了grant之外的所有权限

#针对所有库的授权:*.*
grant select on *.* to 'egon1'@'localhost' identified by '123'; #只在user表中可以查到egon1用户的select权限被设置为Y

#针对某一数据库：db1.*
grant select on db1.* to 'egon2'@'%' identified by '123'; #只在db表中可以查到egon2用户的select权限被设置为Y

#针对某一个表：db1.t1
grant select on db1.t1 to 'egon3'@'%' identified by '123';  #只在tables_priv表中可以查到egon3用户的select权限

#针对某一个字段：
mysql> select * from t3;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | egon1 |   18 |
|    2 | egon2 |   19 |
|    3 | egon3 |   29 |
+------+-------+------+

grant select (id,name),update (age) on db1.t3 to 'egon4'@'localhost' identified by '123'; 
#可以在tables_priv和columns_priv中看到相应的权限
mysql> select * from tables_priv where user='egon4'\G
*************************** 1. row ***************************
       Host: localhost
         Db: db1
       User: egon4
 Table_name: t3
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv:
Column_priv: Select,Update
row in set (0.00 sec)

mysql> select * from columns_priv where user='egon4'\G
*************************** 1. row ***************************
       Host: localhost
         Db: db1
       User: egon4
 Table_name: t3
Column_name: id
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 2. row ***************************
       Host: localhost
         Db: db1
       User: egon4
 Table_name: t3
Column_name: name
  Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 3. row ***************************
       Host: localhost
         Db: db1
       User: egon4
 Table_name: t3
Column_name: age
  Timestamp: 0000-00-00 00:00:00
Column_priv: Update
rows in set (0.00 sec)

#删除权限
revoke select on db1.* from 'alex'@'%';

例如:
在cmd里面执行
revoke select,insert,delete,update *.* from 'root'@'192.168.15.13'
或者在navicat,pycharm里面都可以执行,加上;即可,
最后我们需要在任务管理器里面的服务里面找到mysql服务然后把它重启服务即可彻底解除绑定

权限相关操作

 

权限命令: