Python 第九篇：队列Queue、生产者消费者模型、(IO/异步IP/Select/Poll/Epool)、Mysql操作

时间：2023-03-08 16:57:53
Python 第九篇：队列Queue、生产者消费者模型、(IO/异步IP/Select/Poll/Epool)、Mysql操作

Mysql操作：

grant select,insert,update,delete on *.* to root@"%" Identified by "123456";  #授权远程访问

create database s12day9 charset utf8; #创建支持中文的数据库

创建表：

create table students
(
id int not null auto_increment primary key,
name char(32) not null,
sex char(20) not null,
age tinyint unsigned not null,
tel char(13) null default "-"
);

desc students; #显示表结构

mysql> desc students;

+-------+---------------------+------+-----+---------+----------------+

| Field | Type                | Null | Key | Default | Extra          |

+-------+---------------------+------+-----+---------+----------------+

| id    | int(11)             | NO   | PRI | NULL    | auto_increment |

| name  | char(32)            | NO   |     | NULL    |                |

| sex   | char(20)            | NO   |     | NULL    |                |

| age   | tinyint(3) unsigned | NO   |     | NULL    |                |

| tel   | char(13)            | YES  |     | -       |                |

+-------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

show create table  students;  #查看一个表创建时候的命令

mysql> show create table  students\G;

*************************** 1. row ***************************

       Table: students

Create Table: CREATE TABLE `students` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` char(32) NOT NULL,

  `sex` char(20) NOT NULL,

  `age` tinyint(3) unsigned NOT NULL,

  `tel` char(13) DEFAULT '-',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

insert into students(name,sex,age,tel) values('tom','man',28,'18600033313');

insert into students(name,sex,age,tel) values('jack','man',18,'18600033312'); #插入数据

mysql> insert into students(name,sex,age,tel) values('tom','man',20,'');

Query OK, 1 row affected (0.00 sec)

select * from students; #查询数据

+----+------+-----+-----+-------------+

| id | name | sex | age | tel         |

+----+------+-----+-----+-------------+

|  1 | alex | man |  18 | 151515151   |

|  2 | jack | man |  18 | 18600033312 |

|  3 | tom  | man |  28 | 18600033313 |

|  4 | tom  | man |  20 | 18600033313 |

+----+------+-----+-----+-------------+

4 rows in set (0.00 sec)

select * from students where age > 18 and name = "tom"; #组合条件查询

mysql> select * from students where age > 18 and name = "tom";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel         |

+----+------+-----+-----+-------------+

|  3 | tom  | man |  28 | 18600033313 |

|  4 | tom  | man |  20 | 18600033313 |

+----+------+-----+-----+-------------+

2 rows in set (0.00 sec)

select * from students where age like "2%"; #模糊查询

mysql> select * from students where age like "2%";

+----+------+-----+-----+-------------+

| id | name | sex | age | tel         |

+----+------+-----+-----+-------------+

|  3 | tom  | man |  28 | 18600033313 |

|  4 | tom  | man |  20 | 18600033313 |

+----+------+-----+-----+-------------+

2 rows in set (0.00 sec)

update students set name = "aaa" where name = "alex"; #更新数据

mysql> update students set name = "aaa" where name = "alex";

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

update students set age = 25; #批量改数据

mysql> update students set age = 25;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4  Changed: 4  Warnings: 0

delete from students where name = "aaa"; #删除数据

mysql> delete from students where name = "aaa";

Query OK, 1 row affected (0.00 sec)

alter table students add column nal char(32); #插入一个字段:

mysql> select * from students;

+----+------+-----+-----+-------------+------+

| id | name | sex | age | tel         | nal  |

+----+------+-----+-----+-------------+------+

|  2 | jack | man |  25 | 18600033312 | NULL |

|  3 | tom  | man |  25 | 18600033313 | NULL |

|  4 | tom  | man |  25 | 18600033313 | NULL |

+----+------+-----+-----+-------------+------+

3 rows in set (0.00 sec)

Mysql操作数据库：

pyton3操作数据库：使用pymysql模块：

import  pymysql

conn  = pymysql.connect(host="10.16.59.102",user="root",passwd="",db="s12day9") #定义主机地址、端口、用户和密码

cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在s10day12

reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel','F',26,3663,"US")) #添加数据

reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack','F',23,135343,"CN")) #添加数据

conn.commit() #提交更改，在python 3不提交也是写入到数据库的

conn.close() #关闭conn连接

print(reCount)

python2操作数据库：使用MySQLdb模块：

#/usr/bin/env  python

# -*- coding:utf-8 -*-

import  MySQLdb

conn  = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码

cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上

#reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack1','F',3,135343,"CN"))

#reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel1','F',6,3663,"US"))

reCount = cur.execute("select * from students")

data = cur.fetchall() #取到的所有数据

print(data)

data1 = data[0] #取获取到的第一个数据

print(list(data1)) #将获取到的数据转换为列表，以方便做下一步操作

cur.close() #关闭游标

conn.close() #关闭会话

执行结果：

C:\Python27\python.exe C:/Users/zhang/PycharmProjects/untitled1/python_mysql.py

((33L, 'tom', 'F', 26, '', 'US'), (73L, 'Rachel', 'F', 6, '', 'US'), (72L, 'Jack', 'F', 3, '', 'CN'), (74L, 'Jack', 'F', 3, '', 'CN'), (75L, 'Rachel', 'F', 6, '', 'US'), (76L, 'Jack1', 'F', 3, '', 'CN'), (77L, 'Rachel1', 'F', 6, '', 'US'))

[33L, 'tom', 'F', 26, '', 'US']

批量插入数据：

#/usr/bin/env  python

# -*- coding:utf-8 -*-

import  MySQLdb

conn  = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码

cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上

data =[

     ('a1','M',11,111,'CN'),

     ('a2','F',19,111,'JP'),

     ('a3','F',11,111,'USA'),

     ('a4','F',11,555,'UK'),

]  #将列表插入到数据库，数据格式与表中的格式需一致

reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',li)  #使用sql通过Python模块将数据插入到MySQL，值为变量，会将后面的列表data传递进去

cur.close() #关闭游标

conn.close() #关闭会话

print(reCount)

事物回滚：

回滚需要表的引擎必须为InnoDB才行，因为MySAM是无事物引擎

#/usr/bin/env  python

# -*- coding:utf-8 -*-

import  MySQLdb

conn  = MySQLdb.connect(host="localhost",user="root",passwd="zhang@123",db="s11day12") #实例化对象并定义主机地址、端口、用户和密码

cur = conn.cursor() #连接数据库之后的状态，游标，现在在是在当前数据库之上

reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Jack1','F',3,135343,"CN"))

reCount = cur.execute('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)',('Rachel1','F',6,3663,"US"))

conn.rollback()

conn.commit()

cur.close() #关闭游标

conn.close() #关闭会话

print(reCount)

相关文章