第六章

1.mysql

mysql设置密码

mysqladmin -uroot -p123 password123.com
mysqladmin -uroot password123.com
grant select,insert,update,drop,delete on *.* to test@192.168.1.81 identified by '123.com';

　　查看表

show tables;

select * from mysql.user limit 3;
select * from mysql.user limit 3G

查看表排序

mysql> select * from student order by stu_id;
+--------+-------+-----+---------------+
| stu_id | name  | age | pegister_date |
+--------+-------+-----+---------------+
|      1 | zhang |  22 | 2017-09-21    |
|      2 | li    |  22 | 2017-09-21    |
+--------+-------+-----+---------------+

　　查看反响排序

mysql> select * from student order by stu_id desc;
+--------+-------+-----+---------------+
| stu_id | name  | age | pegister_date |
+--------+-------+-----+---------------+
|      2 | li    |  22 | 2017-09-21    |
|      1 | zhang |  22 | 2017-09-21    |
+--------+-------+-----+---------------+

　　查看变结构

desc mysql；
show create table student;

　　创建数据库

create database python charset utf8;

　　插入

mysql> insert into student2(name,age,register_date) values (
    -> 'han',22,'2017-09-20');

　　更新

update abc set name = 'windows' where id =2;

　　删除

delete from abc where id=22;

　　like筛选

select * from abc where name like "a%";

　　设置主键

mysql> create table student2( 
    -> stu_id int not null auto_increment, 
    -> name char(10), 
    -> age int not null, 
    -> register_date date, 
    -> primary key (stu_id) 
    -> );
Query OK, 0 rows affected (0.02 sec)


mysql> desc student;
+---------------+---------+------+-----+---------+-------+
| Field         | Type    | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| stu_id        | int(11) | NO   | PRI | NULL    |       |
| age           | int(11) | NO   |     | NULL    |       |
| register_date | date    | YES  |     | NULL    |       |
+---------------+---------+------+-----+---------+-------+

　　实例解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

外键

mysql> create table `abc` (
    -> `id` int(11) not null,
    -> `name` char(16) not null,
    -> `class_id` int(11) not null,
    -> primary key (`id`),
    -> key `fk_class_key` (`class_id`),
    -> constraint `fk_class_key` foreign key (`class_id`) references `class` (`id`));
Query OK, 0 rows affected (0.02 sec)

abc 的class_id 关联到 class 的id ,class变被abc关联到如果class 删除id报错，测需要先删除abc的class_id

mysql> insert into class(id,name) values(11,"ux");
Query OK, 1 row affected (0.00 sec)

mysql> insert into abc (id,name,class_id) values(22,'alex', 11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from abc;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | alex |        1 |
|  2 | linu |        0 |
|  3 | linu |        0 |
|  6 | san  |        0 |
|  9 | alex |        9 |
| 22 | alex |       11 |
+----+------+----------+
6 rows in set (0.00 sec)

mysql> select * from student2;
+--------+------+-----+---------------+
| stu_id | name | age | register_date |
+--------+------+-----+---------------+
|      1 | han  |  22 | 2017-09-20    |
|      2 | han  |  22 | 2017-09-20    |
+--------+------+-----+---------------+
2 rows in set (0.00 sec)

分组(group,sum(求和)　

mysql> select name,sum(age) from student group by name with rollup;
+-------+----------+
| name  | sum(age) |
+-------+----------+
| li    |       22 |
| zhang |       22 |
| NULL  |       44 |
+-------+----------+

　　计算总数(count)

计算总数(count)
mysql> select name,count(*) from student group by name;
+-------+----------+
| name  | count(*) |
+-------+----------+
| li    |        1 |
| zhang |        1 |
+-------+----------+

　 alter修改表结构

modify　

alter table abc modify name char(10); 修改为10 
alter table abc modify bbq bigint not null default 200; 修改默认值

　 bigint

alter table abc change id iid bigint; 修改字段名
alter table abc change name name int; 修改字段表类型

　　add

alter table abc add bbq int not null default 100; 添加字段设置默认值

　　drop

alter table aaa drop  bbq; 删除字段

MySQL NULL 值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

集合

create table A (a int not null);
create table B (b int not null);

　　inner join(交集)

select * from A inner join B on A.a = B.b; 
select A.*,B.* from A,B where A.a = B.b;

　　left join(差集)

mysql> select * from A left join B on A.a = B.b;
+-----+------+
| a   | b    |
+-----+------+
|   1 |    1 |
|   2 |    2 |
|   3 |    3 |
| 100 | NULL |
| 200 | NULL |
+-----+------+
mysql> select * from A right join B on A.a = B.b;
+------+---+
| a    | b |
+------+---+
|    3 | 3 |
|    1 | 1 |
|    2 | 2 |
| NULL | 5 |
| NULL | 9 |
| NULL | 8 |
+------+---+
6 rows in set (0.00 sec)

　　并集

mysql> select * from A left join B on A.a = B.b union select * from A right join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|  100 | NULL |
|  200 | NULL |
| NULL |    5 |
| NULL |    9 |
| NULL |    8 |
+------+------+

　　mysql事务

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。

在Mysql控制台使用事务来操作

mysql> begin； ＃开始一个事务
 
mysql> insert into a (a) values(555);
 
mysql>rollback; 回滚 ， 这样数据是不会写入的
当然如果上面的数据没问题，就输入commit提交命令就行

　　mysql索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

普通索引--创建索引

create index index_name on A (a(32);

主键也是一种索引
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

创建表的时候直接创建索引

CREATE TABLE mytable( 
  
ID INT NOT NULL,  
  
username VARCHAR(16) NOT NULL, 
  
INDEX [indexName] (username(length)) 
  
);

　　删除索引的语法

DROP INDEX [indexName] ON mytable

　　查看索引

mysql> show index from test2;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test2 |          0 | test_name |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

　　唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式

create unique index test_name on test2(name(32);

　　创建表的时候直接指定

CREATE TABLE mytable( 
  
ID INT NOT NULL,  
  
username VARCHAR(16) NOT NULL, 
  
UNIQUE [indexName] (username(length)) 
  
);

2.pymysql

mysqldb不支持3.0 3.0可以用pymysql
python-mysqldb : http://www.cnblogs.com/wupeiqi/articles/5095821.html
pymysql : http://www.cnblogs.com/wupeiqi/articles/5713330.html

查询

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql

conn = pymysql.connect(host='192.168.80.12',user='test',passwd='123.com',db='python')
cur = conn.cursor()

recount = cur.execute('select * from student')
# print(cur.fetchone())
# print(cur.fetchone())
print('------------')
print(cur.fetchall())

cur.close()
conn.close()

　　插入

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql

conn = pymysql.connect(host='192.168.80.12',user='test',passwd='123.com',db='python')
cur = conn.cursor()
li = [
    ('zhang',18,'2017-09-25'),
    ('wang',23,'2017-09-25')
]
recount = cur.executemany('insert into student(name,age,pegister_date) values (%s,%s,%s)',li)

conn.commit()
cur.close()
conn.close()
print(recount)

　　更新

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql

conn = pymysql.connect(host='192.168.80.12',user='test',passwd='123.com',db='python')
cur = conn.cursor()

recount = cur.execute('update student set age = %s where stu_id = 18',(22,))

conn.commit()
cur.close()
conn.close()
print(recount)

　　删除

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql

conn = pymysql.connect(host='192.168.80.12',user='test',passwd='123.com',db='python')
cur = conn.cursor()

recount = cur.execute('delete from student where stu_id = 23')

conn.commit()
cur.close()
conn.close()
print(recount)

3.sqlalchemy

创建表

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 
10 
11 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
12                        encoding='utf-8',echo=True)
13 
14 base = declarative_base()       #生成基类
15 
16 class user(base):
17     __tablename__ = 'user'      #表名
18     id = Column(Integer,primary_key=True)  #设置主键，自增加一
19     name = Column(String(32))              #设置name长度32
20     password = Column(String(64))          
21 
22 base.metadata.create_all(engine)          #创建

View Code

添加数据

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 
11 engine = create_engine('mysql+pymysql://test:123.com@192.168.1.82/python',
12                        encoding='utf-8',echo=True)
13 
14 base = declarative_base()
15 
16 class user(base):
17     __tablename__ = 'user'                    #表名
18     id = Column(Integer,primary_key=True)      #设置主键，自增加一
19     name = Column(String(32))                  #长度
20     password = Column(String(64))
21 
22 base.metadata.create_all(engine)
23 
24 session_class = sessionmaker(bind=engine)
25 session = session_class()
26 user_obj = user(name='alex',password='alex123') #设置字段
27 user_obj2 = user(name='jack',password='123')
28 
29 
30 session.add(user_obj)                         #添加
31 session.add(user_obj2)
32 
33 print(user_obj.name,user_obj.id)
34 session.commit()                             #提交

View Code

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 
11 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
12                        encoding='utf-8')
13 
14 base = declarative_base()
15 
16 class user(base):
17     __tablename__ = 'user'                    #表名
18     id = Column(Integer,primary_key=True)      #设置主键，自增加一
19     name = Column(String(32))                  #长度
20     password = Column(String(64))
21 
22     # def __repr__(self):
23     #     return "<%s name:%s>" %(self.id,self.name)
24 
25 base.metadata.create_all(engine)
26 
27 session_class = sessionmaker(bind=engine)
28 session = session_class()
29 my_user = session.query(user).filter_by(name='alex').first()
30 #my_user = session.query(user).filter_by(name='alex').all()
31 print(my_user.id,my_user.name,my_user.password)
32 # my_user = session.query(user).filter_by().all()
33 # print(my_user[0].name,my_user[0].password)
34 
35 ###
36 my_user = session.query(user).filter(user.id > 1).filter(user.id < 4).first()

查询数据

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 
11 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
12                        encoding='utf-8')
13 
14 base = declarative_base()
15 
16 class user(base):
17     __tablename__ = 'user'                    #表名
18     id = Column(Integer,primary_key=True)      #设置主键，自增加一
19     name = Column(String(32))                  #长度
20     password = Column(String(64))
21 
22 
23 
24 base.metadata.create_all(engine)
25 
26 session_class = sessionmaker(bind=engine)
27 session = session_class()
28 my_user = session.query(user).filter_by(name='alex').first()
29 
30 my_user.name = 'test'
31 
32 session.commit()

修改

统计和分组

my=session.query(user).filter(user.name.like('t%')).count()
print(my)

from sqlalchemy import func
print(session.query(func.count(user.name),user.name).group_by(user.name).all())

　　删除

session.query(user).filter(user.id > 1).delete()
session.commit()

　　回滚

my_user = session.query(user).filter_by(id=1).first()   #修改
my_user.name = 'Jack'

fake_user = user(name='rain',password='12345')         #添加
session.add(fake_user)

print(session.query(user).filter(user.name.in_(['Jack','rain'])).all())

session.rollback() #回滚

print(session.query(user).filter(user.name.in_(['Jack','rain'])).all())

#session.commit()

　　多表查询

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 from sqlalchemy import ForeignKey
11 
12 engine = create_engine('mysql+pymysql://test:123.com@192.168.1.82/python',
13                        encoding='utf-8',echo=True)
14 
15 base = declarative_base()
16 
17 class studyrecord(base):
18     __tablename__ = 'study_record'
19     id = Column(Integer,primary_key=True)
20     day = Column(Integer,nullable=False)
21     status = Column(String(32), nullable=False)
22     stu_id = Column(Integer, nullable=False)
23 
24     def __repr__(self):
25         return "<%s name:%s>" % (self.id, self.day)
26 
27 class Student(base):
28     __tablename__ = 'student'
29     id = Column(Integer,primary_key=True)
30     name = Column(String(32),nullable=False)
31     register_date = Column(nullable=False)
32 
33 
34     def __repr__(self):
35         return "<%s name:%s>" % (self.id, self.name)
36         
37 session_class = sessionmaker(bind=engine)
38 session = session_class()
39 print(session.query(studyrecord,Student).filter(studyrecord.id==Student.id).all())

View Code

外键

mysql必须源码 mysql5.62.7
grant all privileges on *.* to test@'%' identified by '123.com' with grant option;

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 from sqlalchemy import func
11 from sqlalchemy import ForeignKey
12 from sqlalchemy.orm import relationship
13 
14 engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
15                        encoding='utf-8')
16 
17 base = declarative_base()
18 
19 class Student(base):
20     __tablename__ = "student"
21     id = Column(Integer,primary_key=True)
22     name = Column(String(32),nullable=False)
23     register_date = Column(Integer,nullable=False)
24 
25 
26 
27 class studyrecord(base):
28     __tablename__ = 'study_record'
29     id = Column(Integer,primary_key=True)
30     day = Column(Integer,nullable=False)
31     status = Column(String(32),nullable=False)
32     stu_id = Column(Integer,ForeignKey("student.id"))  #关联student表
33     student = relationship('Student',backref='my')    通过student可以查student表所有数据，而student表可以通过my查studyrecord表所有数据 
34 
35 
36 base.metadata.create_all(engine)

View Code

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import sqlalchemy
 5 from sqlalchemy import create_engine
 6 from sqlalchemy.ext.declarative import declarative_base
 7 from sqlalchemy import Column,Integer,String
 8 from sqlalchemy.orm import sessionmaker
 9 from sqlalchemy import ForeignKey
10 
11 engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
12                        encoding='utf-8')
13 
14 base = declarative_base()
15 
16 class studyrecord(base):
17     __tablename__ = 'study_record'
18     id = Column(Integer,primary_key=True)
19     day = Column(Integer,nullable=False)
20     status = Column(String(32), nullable=False)
21     stu_id = Column(Integer, nullable=False)
22 
23     def __repr__(self):
24         return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)
25 
26 class Student(base):
27     __tablename__ = 'student'
28     id = Column(Integer,primary_key=True)
29     name = Column(String(32),nullable=False)
30     register_date = Column(nullable=False)
31 
32     def __repr__(self):
33         return "<%s name:%s>" % (self.id ,self.name )
34 
35 
36 
37 
38 s1 = Student(name='alex',register_date='201170926')
39 s2 = Student(name='jack',register_date='201170926')
40 s3 = Student(name='rain',register_date='201170926')
41 s4 = Student(name='reic',register_date='201170926')
42 
43 t1 = studyrecord(day=1,status='YES',stu_id=1)
44 t2 = studyrecord(day=2,status='NO',stu_id=1)
45 t3 = studyrecord(day=3,status='YES',stu_id=1)
46 t4 = studyrecord(day=1,status='YES',stu_id=2)
47 
48 session_class = sessionmaker(bind=engine)
49 session = session_class()
50 
51 
52 session.add_all([s1,s2,s3,s4,t1,t2,t3,t4])
53 
54 session.commit()

添加数据

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 from sqlalchemy import func
11 from sqlalchemy import ForeignKey
12 from sqlalchemy.orm import relationship
13 
14 engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
15                        encoding='utf-8')
16 
17 base = declarative_base()
18 
19 class Student(base):
20     __tablename__ = "student"
21     id = Column(Integer,primary_key=True)
22     name = Column(String(32),nullable=False)
23     register_date = Column(Integer,nullable=False)
24 
25     def __repr__(self):
26         return "<%s name:%s>" % (self.id ,self.name )    #显示格式
27 
28 class studyrecord(base):
29     __tablename__ = 'study_record'
30     id = Column(Integer,primary_key=True)
31     day = Column(Integer,nullable=False)
32     status = Column(String(32),nullable=False)
33     stu_id = Column(Integer,ForeignKey("student.id"))
34     student = relationship('Student',backref='my')
35 
36     def __repr__(self):
37         return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)         #显示格式
38 
39 session_class = sessionmaker(bind=engine)
40 session = session_class()
41 
42 stu_obj = session.query(Student).filter(Student.name=='alex').first()
43 print(stu_obj.my) #两个查询到的结果

查看

多外键关联

http://www.cnblogs.com/alex3714/articles/5978329.html

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 import pymysql
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 from sqlalchemy import func
11 from sqlalchemy import ForeignKey
12 from sqlalchemy.orm import relationship
13 
14 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
15                        encoding='utf-8')
16 
17 base = declarative_base()
18 
19 class Customer(base):
20     __tablename__ = 'customer'
21     id = Column(Integer,primary_key=True)
22     name = Column(String(64))
23 
24     billing_address_id = Column(Integer,ForeignKey('address.id'))  #关联
25     shipping_address_id = Column(Integer,ForeignKey('address.id')) #关联
26 
27     billing_address = relationship('Address',foreign_keys=[billing_address_id]) #添加
28     shipping_address = relationship('Address',foreign_keys=[shipping_address_id])
29 
30 class Address(base):
31     __tablename__ = 'address'
32     id = Column(Integer,primary_key=True)
33     street = Column(String(64))
34     city = Column(String(64))
35     state = Column(String(64))
36 
37 
38 base.metadata.create_all(engine)     #创建

创建关联

 1 !/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 
 4 __author__ = 'han'
 5 import sqlalchemy
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import Column,Integer,String
 9 from sqlalchemy.orm import sessionmaker
10 from sqlalchemy import ForeignKey
11 from sqlalchemy.orm import relationship
12 
13 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
14                        encoding='utf-8')
15 
16 base = declarative_base()
17 
18 class Customer(base):
19     __tablename__ = 'customer'
20     id = Column(Integer,primary_key=True)
21     name = Column(String(64))
22     billing_address_id = Column(Integer,ForeignKey("address.id"))
23     shipping_address_id = Column(Integer,ForeignKey("address.id"))
24 
25 
26 class Address(base):
27     __tablename__ = 'address'
28     id = Column(Integer, primary_key=True)
29     street = Column(String(64))
30     city = Column(String(64))
31     state = Column(String(64))
32 
33     def __repr__(self):
34         return self.street

插入数据

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 
 5 from day3 import test2
 6 from sqlalchemy.orm import sessionmaker
 7 
 8 session_class = sessionmaker(bind=test2.engine)
 9 session = session_class()   #生成session实例
10 
11 # addr1 = test2.Address(street="Tiantongyuan", city="Changping", state="BJ")
12 # addr2 = test2.Address(street="Wudaokou", city="Haidian", state="BJ")
13 # addr3 = test2.Address(street="Yanjiao",city="LangFang", state="HB")
14 #
15 # session.add_all([addr1,addr2,addr3])
16 # c1 = test2.Customer(billing_address_id=addr2.id,shipping_address_id=addr2.id,name="Alex")   # 添加数据
17 # c2 = test2.Customer(billing_address_id=addr3.id,shipping_address_id=addr3.id,name="Jack",)
18 #
19 # session.add_all([c1,c2])
20 
21 
22 obj = session.query(test2.Customer).filter(test2.Customer.name =="alex").first()   #查看
23 print(obj.name,obj.billing_address_id,obj.shipping_address_id)
24 
25 session.commit()

插入数据

多对多关联

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 
 5 from sqlalchemy import Table,Column,Integer,String,DATE,ForeignKey
 6 from sqlalchemy.orm import relationship
 7 from sqlalchemy.ext.declarative import declarative_base
 8 from sqlalchemy import create_engine
 9 from sqlalchemy.orm import sessionmaker
10 
11 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
12                        encoding='utf-8')
13 
14 Base = declarative_base()
15 
16 book_m2m_author = Table('book_m2m_author',Base.metadata,
17                         Column('book_id',Integer,ForeignKey('books.id')),
18                         Column('author_id',Integer,ForeignKey('authors.id')),
19                         )
20 
21 class Book(Base):
22     __tablename__ = 'books'
23     id = Column(Integer,primary_key=True)
24     name = Column(String(64))
25     pub_date = Column(DATE)
26     authors = relationship('Author',secondary=book_m2m_author,backref='books')  #通过Author可以查authors和book_m2m_author ，authors和book_m2m_author通过books查books
27 
28     def __repr__(self):
29         return self.name
30 
31 class Author(Base):
32     __tablename__ = 'authors'
33     id = Column(Integer,primary_key=True)
34     name = Column(String(32))
35 
36     def __reor__(self):
37         return self.name
38 
39 Base.metadata.create_all(engine)     #创建

创建关联

 1 #!/usr/bin/env python
 2 # _*_ encoding:utf-8 _*_
 3 __author__ = 'han'
 4 
 5 from day3 import test
 6 from sqlalchemy import create_engine
 7 from sqlalchemy.orm import sessionmaker
 8 
 9 engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
10                        encoding='utf-8')
11 
12 
13 session_class = sessionmaker(bind=test.engine)
14 session = session_class()
15 
16 # b1 = test.Book(name="learn python with Alex",pub_date="2017-09-29")
17 # b2 = test.Book(name="learn Zhuangbility with Alex",pub_date="2017-09-29")
18 # b3 = test.Book(name="learn hook up girls with Alex",pub_date="2017-09-29")
19 #
20 # a1 = test.Author(name='Alex')
21 # a2 = test.Author(name='Jack')
22 # a3 = test.Author(name='Abc')
23 #
24 # b1.authors = [a1,a3]    #添加关联
25 # b2.authors = [a1,a2,a3] #添加关联
26 #
27 # session.add_all([b1,b2,b3,a1,a2,a3])
28 
29 
30 ###查看数据
31 author_obj = session.query(test.Author).filter(test.Author.name=='alex').first()
32 # print(author_obj.books[1].pub_date)
33 book_obj = session.query(test.Book).filter(test.Book.id==2).first()
34 print(author_obj.name , author_obj.books)
35 
36 session.commit()

插入数据和查看

多对多删除

删除数据时不用管boo_m2m_authors　， sqlalchemy会自动帮你把对应的数据删除

通过书删除作者

author_obj =s.query(Author).filter_by(name="Jack").first()
book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()
book_obj.authors.remove(author_obj) #从一本书里删除一个作者
s.commit()

　　直接删除作者　

删除作者时，会把这个作者跟所有书的关联关系数据也自动删除

author_obj =s.query(Author).filter_by(name="Alex").first()
# print(author_obj.name , author_obj.books)
s.delete(author_obj)
s.commit()

　　处理中文

sqlalchemy设置编码字符集一定要在数据库访问的URL上增加charset=utf8，否则数据库的连接就不是utf8的编码格式

eng = create_engine('mysql://root:root@localhost:3306/test2?charset=utf8',echo=True)

4.修改paramiko源码

github搜索下载paramiko
解压拷贝demos
执行demo.py输入ip,username,password
python3.0需要修改interactive.py 84行为decode()

修改2

修改 interactive.p搜索 chan.send

if sys.stdin in r:
            x = sys.stdin.read(1)
            if len(x) == 0:
                break
            if x == "
":
                cmd_str = "".join(cmd)
                print("-->",cmd_str)
                cmd = []
            else:
                 cmd.append(x)
            chan.send(x)

第六章

目录

1.mysql

2.pymysql

3.sqlalchemy

多对多删除

4.修改paramiko源码