第六章

目录

1. mysql
2. pymysql
3. sqlalchemy
4. 修改paramiko源码

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

     创建表

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String


engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
                       encoding='utf-8',echo=True)

base = declarative_base()       #生成基类

class user(base):
    __tablename__ = 'user'      #表名
    id = Column(Integer,primary_key=True)  #设置主键，自增加一
    name = Column(String(32))              #设置name长度32
    password = Column(String(64))          

base.metadata.create_all(engine)          #创建

    添加数据

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:123.com@192.168.1.82/python',
                       encoding='utf-8',echo=True)

base = declarative_base()

class user(base):
    __tablename__ = 'user'                    #表名
    id = Column(Integer,primary_key=True)      #设置主键，自增加一
    name = Column(String(32))                  #长度
    password = Column(String(64))

base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session = session_class()
user_obj = user(name='alex',password='alex123') #设置字段
user_obj2 = user(name='jack',password='123')


session.add(user_obj)                         #添加
session.add(user_obj2)

print(user_obj.name,user_obj.id)
session.commit()                             #提交

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
                       encoding='utf-8')

base = declarative_base()

class user(base):
    __tablename__ = 'user'                    #表名
    id = Column(Integer,primary_key=True)      #设置主键，自增加一
    name = Column(String(32))                  #长度
    password = Column(String(64))

    # def __repr__(self):
    #     return "<%s name:%s>" %(self.id,self.name)

base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session = session_class()
my_user = session.query(user).filter_by(name='alex').first()
#my_user = session.query(user).filter_by(name='alex').all()
print(my_user.id,my_user.name,my_user.password)
# my_user = session.query(user).filter_by().all()
# print(my_user[0].name,my_user[0].password)

###
my_user = session.query(user).filter(user.id > 1).filter(user.id < 4).first()

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.12/python',
                       encoding='utf-8')

base = declarative_base()

class user(base):
    __tablename__ = 'user'                    #表名
    id = Column(Integer,primary_key=True)      #设置主键，自增加一
    name = Column(String(32))                  #长度
    password = Column(String(64))



base.metadata.create_all(engine)

session_class = sessionmaker(bind=engine)
session = session_class()
my_user = session.query(user).filter_by(name='alex').first()

my_user.name = 'test'

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()

　　多表查询

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey

engine = create_engine('mysql+pymysql://test:123.com@192.168.1.82/python',
                       encoding='utf-8',echo=True)

base = declarative_base()

class studyrecord(base):
    __tablename__ = 'study_record'
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32), nullable=False)
    stu_id = Column(Integer, nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id, self.day)

class Student(base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(nullable=False)


    def __repr__(self):
        return "<%s name:%s>" % (self.id, self.name)
        
session_class = sessionmaker(bind=engine)
session = session_class()
print(session.query(studyrecord,Student).filter(studyrecord.id==Student.id).all())

      外键

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

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
                       encoding='utf-8')

base = declarative_base()

class Student(base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(Integer,nullable=False)



class studyrecord(base):
    __tablename__ = 'study_record'
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id"))  #关联student表
    student = relationship('Student',backref='my')    通过student可以查student表所有数据，而student表可以通过my查studyrecord表所有数据 


base.metadata.create_all(engine)

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey

engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
                       encoding='utf-8')

base = declarative_base()

class studyrecord(base):
    __tablename__ = 'study_record'
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32), nullable=False)
    stu_id = Column(Integer, nullable=False)

    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)

class Student(base):
    __tablename__ = 'student'
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id ,self.name )




s1 = Student(name='alex',register_date='201170926')
s2 = Student(name='jack',register_date='201170926')
s3 = Student(name='rain',register_date='201170926')
s4 = Student(name='reic',register_date='201170926')

t1 = studyrecord(day=1,status='YES',stu_id=1)
t2 = studyrecord(day=2,status='NO',stu_id=1)
t3 = studyrecord(day=3,status='YES',stu_id=1)
t4 = studyrecord(day=1,status='YES',stu_id=2)

session_class = sessionmaker(bind=engine)
session = session_class()


session.add_all([s1,s2,s3,s4,t1,t2,t3,t4])

session.commit()

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://max:maxkim@192.168.1.81/test',
                       encoding='utf-8')

base = declarative_base()

class Student(base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True)
    name = Column(String(32),nullable=False)
    register_date = Column(Integer,nullable=False)

    def __repr__(self):
        return "<%s name:%s>" % (self.id ,self.name )    #显示格式

class studyrecord(base):
    __tablename__ = 'study_record'
    id = Column(Integer,primary_key=True)
    day = Column(Integer,nullable=False)
    status = Column(String(32),nullable=False)
    stu_id = Column(Integer,ForeignKey("student.id"))
    student = relationship('Student',backref='my')

    def __repr__(self):
        return "<%s day:%s status:%s>" % (self.student.name,self.day,self.status)         #显示格式

session_class = sessionmaker(bind=engine)
session = session_class()

stu_obj = session.query(Student).filter(Student.name=='alex').first()
print(stu_obj.my) #两个查询到的结果

    多外键关联

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

#!/usr/bin/env python
# _*_ encoding:utf-8 _*_
__author__ = 'han'
import pymysql
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import func
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
                       encoding='utf-8')

base = declarative_base()

class Customer(base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))

    billing_address_id = Column(Integer,ForeignKey('address.id'))  #关联
    shipping_address_id = Column(Integer,ForeignKey('address.id')) #关联

    billing_address = relationship('Address',foreign_keys=[billing_address_id]) #添加
    shipping_address = relationship('Address',foreign_keys=[shipping_address_id])

class Address(base):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))


base.metadata.create_all(engine)     #创建

!/usr/bin/env python
# _*_ encoding:utf-8 _*_

__author__ = 'han'
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
from sqlalchemy.orm import sessionmaker
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
                       encoding='utf-8')

base = declarative_base()

class Customer(base):
    __tablename__ = 'customer'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    billing_address_id = Column(Integer,ForeignKey("address.id"))
    shipping_address_id = Column(Integer,ForeignKey("address.id"))


class Address(base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))

    def __repr__(self):
        return self.street

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

from day3 import test2
from sqlalchemy.orm import sessionmaker

session_class = sessionmaker(bind=test2.engine)
session = session_class()   #生成session实例

# addr1 = test2.Address(street="Tiantongyuan", city="Changping", state="BJ")
# addr2 = test2.Address(street="Wudaokou", city="Haidian", state="BJ")
# addr3 = test2.Address(street="Yanjiao",city="LangFang", state="HB")
#
# session.add_all([addr1,addr2,addr3])
# c1 = test2.Customer(billing_address_id=addr2.id,shipping_address_id=addr2.id,name="Alex")   # 添加数据
# c2 = test2.Customer(billing_address_id=addr3.id,shipping_address_id=addr3.id,name="Jack",)
#
# session.add_all([c1,c2])


obj = session.query(test2.Customer).filter(test2.Customer.name =="alex").first()   #查看
print(obj.name,obj.billing_address_id,obj.shipping_address_id)

session.commit()

     多对多关联

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

from sqlalchemy import Table,Column,Integer,String,DATE,ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
                       encoding='utf-8')

Base = declarative_base()

book_m2m_author = Table('book_m2m_author',Base.metadata,
                        Column('book_id',Integer,ForeignKey('books.id')),
                        Column('author_id',Integer,ForeignKey('authors.id')),
                        )

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer,primary_key=True)
    name = Column(String(64))
    pub_date = Column(DATE)
    authors = relationship('Author',secondary=book_m2m_author,backref='books')  #通过Author可以查authors和book_m2m_author ，authors和book_m2m_author通过books查books

    def __repr__(self):
        return self.name

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer,primary_key=True)
    name = Column(String(32))

    def __reor__(self):
        return self.name

Base.metadata.create_all(engine)     #创建

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

from day3 import test
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://test:123.com@192.168.80.11/abc',
                       encoding='utf-8')


session_class = sessionmaker(bind=test.engine)
session = session_class()

# b1 = test.Book(name="learn python with Alex",pub_date="2017-09-29")
# b2 = test.Book(name="learn Zhuangbility with Alex",pub_date="2017-09-29")
# b3 = test.Book(name="learn hook up girls with Alex",pub_date="2017-09-29")
#
# a1 = test.Author(name='Alex')
# a2 = test.Author(name='Jack')
# a3 = test.Author(name='Abc')
#
# b1.authors = [a1,a3]    #添加关联
# b2.authors = [a1,a2,a3] #添加关联
#
# session.add_all([b1,b2,b3,a1,a2,a3])


###查看数据
author_obj = session.query(test.Author).filter(test.Author.name=='alex').first()
# print(author_obj.books[1].pub_date)
book_obj = session.query(test.Book).filter(test.Book.id==2).first()
print(author_obj.name , author_obj.books)

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)