python_way day12 sqlalchemy，原生mysql命令

python_way day12 sqlalchemy，mysql原生命令

1、sqlalchemy

2、mysql 原生命令

一，sqlalchemy

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

中间状态<br><br>from sqlalchemy import  create_engine , Table, Column, Integer, String, MetaData,ForeignKey
#MetaData 里面封装了很多类被创建后的属性，ForeignKey外键,Column 队列 ， integer 证书 ， string 字符串
metadata = MetaData
#相当于继承父类
 
user = Table('user', metadata,
             Column('id', Integer, primary_key=True),
             Column('name', String))
 
#Table 创建一个user表的对象
 
color = Table('color', metadata,
                Column('id',Integer,primary_key=True),
                Column('name',String(20)),)
 
engine = create_engine('mysql+pymysql://r:<password>@<host>/<dbname>[?<options>]')<br>#连接数据库<br>metadata.create_all(engine) <br>#创建所有跟metadata关联的表格<br><br>conn.engine.connect() <br>#获取mysql游标<br><br>sql = user.insert().values(name="alex")<br>conn.execute(sql)  #相当于执行上面的命令<br>sql = user.delete().where(user.c.id >1)

也可以通过 name 删除

1	`修改<br><br>`

查找

完美状态

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)

Base = declarative_base()

# 创建单表
class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    extra = Column(String(16))

    __table_args__ = (
    UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra'),
    )

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
 
engine = create_engine('mysql+pymysql://root:hanxu@192.168.109.129:3306/asset',max_overflow=5,echo=True)  #echo=True 看过程Base = declarative_base()  #封装了metadata，生成了一个SqlORM的基类
# 创建单表
class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True,autoincrement=True)  #id整数，主键, 自增，
    hostname = Column(String(64),unique=True,nullable=False)       #name string,唯一，非空
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
 
Base.metadata.create_all(engine) #执行上面的sqlif __name__ == '__main__':    SessionCls = sessionmaker(bind=engine)   #创建与数据库的回话session class #注意了这里创建的是一个类，而不是一个实例    session = SessionCls()     #这次是连接的实例    h1 = Host(hostname='localhost',ip_addr='127.0.0.1')   #给Host增加一条数据    h2 = Host(hostname="ubantu",ip_addr='192.168.0.2',port=5500)    # session.add(h1)#注册创建1个    session.add_all([h1,h2]) #注册全部创建    session.commit() #提交#以上步骤可以单独执行，也可以一起执行#查询class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True,autoincrement=True)  #id整数，主键, 自增，
    hostname = Column(String(64),unique=True,nullable=False)       #name string,唯一，非空
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
 
Base.metadata.create_all(engine) #执行上面的sqlif __name__ == '__main__':    SessionCls = sessionmaker(bind=engine)   #创建与数据库的回话session class #注意了这里创建的是一个类，而不是一个实例    session = SessionCls()     #这次是连接的实例    obj = session.query(Host).filter(Host.hostname=='localhost').first()     obj = session.query(Host).filter(Host.hostname=='localhost').all()    print(obj)  #obj是个对象，获取到他后就可以对他进行修改了    session.commit()#删除class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True,autoincrement=True)  #id整数，主键, 自增，
    hostname = Column(String(64),unique=True,nullable=False)       #name string,唯一，非空
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
 
Base.metadata.create_all(engine) #执行上面的sqlSessionCls = sessionmaker(bind=engine)   #创建与数据库的回话session class #注意了这里创建的是一个类，而不是一个实例session = SessionCls()     #这次是连接的实例h1 = Host(hostname='suse',ip_addr='172.168.0.19')   #给Host增加一条数据session.add(h1)#注册创建1个obj = session.query(Host).filter(Host.hostname=='centos').first()   #查询且过滤 hostname = lcoalhostsession.delete(obj)     　　　　　　　　　　　　　　　　　　　　　　　　　 #把centos查询到的这个对象删除session.commit()　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 #提交　　　　　filter_by:后面的 = 只用写一个就可以了obj = session.query(Host).filter_by(Host.hostname='centos').first()#其他写法obj = session.query(Host).filter(Host.hostname.like('%en%')).first() 　

#执行后显示的过程（哈哈，以后不怕不会写sql语句了，nm 太强大了！）

模糊查询
in_ 在
obj = session.query(Host).filter(Host.hostname.in_(['cen','suse'])).first() 
#查询到的结果如果在cen，suse里 则匹配
<br>or_ 或<br># obj = session.query(Host).filter(or_(Host.hostname.like('ng%'),Host.port > 50)).first(）
查询ng%后面任意的或者port>50的
<br>and_ 与
obj = session.query(Host).filter(and_(Host.hostname.like('ng%'),Host.port > 50)).first()  #查询且过滤 hostname = lcoalhost
查询ng%后面任意的并且port又要大于50<br><br>matchobj = session.query(Host).filter(Host.hostname.match('nginx'))<br>print(obj)

---> SELECT hosts.id AS hosts_id, hosts.hostname AS hosts_hostname, hosts.ip_addr AS hosts_ip_addr, hosts.port AS hosts_port
FROM hosts
WHERE hosts.hostname MATCH :hostname_1 <---

创建多表并且支持一对多的外键

#创建表结构
class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True,autoincrement=True) 
    hostname = Column(String(64),unique=True,nullable=False)     
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer,ForeignKey('work_group.id'))   #创建外面hosts中的group_id关联到work_group的id
    group = relationship("Group")   #要写大写的实例名字通过映射的关系 ， 能在hosts表中查询到外键表中的其他value


class Group(Base):
    __tablename__= 'work_group'
    id = Column(Integer,primary_key=True)       #自动自增,主键
    name = Column(String(64),unique=True,nullable=False)

Base.metadata.create_all(engine) #执行上面的sql

#增加数据
if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)   #创建与数据库的回话session class #注意了这里创建的是一个类，而不是一个实例
    session = SessionCls()     #这次是连接的实例
    #因为host要关联到group里，所以要先创建一个组
    g1 = Group(name='g1')
    g2 = Group(name='g2')
    g3 = Group(name='g3')

    h1 = Host(hostname='nginx1',ip_addr='172.40.0.180',port=50618,group_id=g1.id)   #同时给Host增加一条数据，让h1的group_id 和 g1的id关联
    session.add_all([g1,g2,g3,h1])
    session.commit()

由于host和group同时创建的，所以h1应该关联到g1的id在执行完却没有关联
所以要确保g1先存在

哪怎么去修改哪？

if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)   
    session = SessionCls()    
    obj2 = session.query(Group).filter(Group.name=='g1').first()  　　　　　　　　　　　　　　　　　　#先找出Group对应的work_group表 name == g1 的这行表的对象
    obj1 = session.query(Host).filter(Host.hostname=='nginx1').update({"group_id":obj2.id})　　　 #然后再找出Host表中，hostname对应nginx1的这行表，然后把group_id更新成obj2.id

　　 print（obj2.id） #这样可以查看work_group表的 g1对应的id是什么

查询关联的表里的内容

#创建表结构
class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True,autoincrement=True) 
    hostname = Column(String(64),unique=True,nullable=False)     
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer,ForeignKey('work_group.id'))   #创建外面hosts中的group_id关联到work_group的id
    group = relationship("Group")   #要写大写的实例名字通过映射的关系 ， 能在hosts表中查询到外键表中的其他value


class Group(Base):
    __tablename__= 'work_group'
    id = Column(Integer,primary_key=True)       #自动自增,主键
    name = Column(String(64),unique=True,nullable=False)
　　 host = relationship("Host")　　　　　#这样就是反向也可以关联查询了

Base.metadata.create_all(engine) #执行上面的sql

#现在已经将hosts表中的group_id 和 work_group表中的id关联上了

那我现在想通过hosts的group_id 查询到关联的id里的name怎么做？就用到上面蓝色的那个字段 group = relationship("Group") 的group这个字段

obj1 = session.query(Host).filter(Host.hostname=='nginx1').first()
print(obj1.group.name)


如果觉得麻烦，也可以加一方也能实现，那就是   group = relationship("Group"， backref='host_list')   #backref='host_list' 反向关联Group查询host，查询的关键字叫Host_list

#连表查询

a b

1 3

2 4

3 5

4 6

INNER JOIN ，交集

1	`将相同的查出来并且合并<br>select` `` `from` `a INNER JOIN b on a.a` `=` `b.b<br>select a.` `,b.*` `from` `a,b where a.a` `=` `b.b`

a b

3 3

4 4

LEFT OUTER JOIN

以左边为主表，把相对应的右边的表查询出来
select * from a LEFT OUTER JOIN b on a.a = b.b
select a.* ,b.* from a,b where a.a = b.b（+）

a b

1 NULL

2 NULL

3 3

4 4

FULL OUTER JION

1 2	`把所有的表查询出来` `select` `*` `from` `a FULL OUTER JOIN b on a.a` `=` `b.b`

a 　　 b

1 　　 NULL

2 　　 NULL

3 　　 3

4 4

NULL　　5

NULL　　6

#创建多对多　

1、创建出表结构
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, and_, or_
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine, func ,Table

engine = create_engine('mysql+pymysql://root:hanxu@192.168.109.129:3306/asset',max_overflow=5,echo=True)  #echo=True 看过程

Base = declarative_base()  #封装了metadata，生成了一个SqlORM的基类
# 创建单表

#使用metadata方式创建一个 HostGroup 2个表的关联表
Host2Group = Table('host_2_group', Base.metadata,
      Column('host_id',ForeignKey('host.id'),primary_key=True),　　　　   #host表中的id
      Column('group_id',ForeignKey('work_group.id'),primary_key=True),　　#group表中的id　　
                   )

class Host(Base):  #所有的子类都继承这个基类
    #创建表结构
    __tablename__ = 'host'
    id = Column(Integer, primary_key=True,autoincrement=True)  #id整数，主键, 自增，
    hostname = Column(String(64),unique=True,nullable=False)       #name string,唯一，非空
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group = relationship("Group", secondary=Host2Group,backref='host_list')   #backref='host_list' 反向关联Group查询host，查询的关键字叫Host_list


class Group(Base):
    __tablename__= 'work_group'
    id = Column(Integer,primary_key=True)       　　　　　　#自动自增
    name = Column(String(64),unique=True,nullable=False)

Base.metadata.create_all(engine) #执行上面的sql

2，创建组的表
if __name__ == '__main__':
    SessionCls = sessionmaker(bind=engine)   #创建与数据库的回话session class #注意了这里创建的是一个类，而不是一个实例
    session = SessionCls()     #这次是连接的实例
    # #因为host要关联到group里，所以要先创建一个组

    g1 = Group(name='g1')
    g2 = Group(name='g2')
    g3 = Group(name='g3')
    g4 = Group(name='g4')
    session.add_all([g1,g2,g3,g4])
　　 session.commit()

3,创建主机
h1 = Host(hostname='nginx',ip_addr='172.40.0.175',port=50618)   #给Host增加一条数据
    h2 = Host(hostname='mysql',ip_addr='172.40.0.176')               #给Host增加一条数据
    h3 = Host(hostname='java',ip_addr='172.50.0.177',port=50617)    #给Host增加一条数据
    session.commit() #提交

4、将host和group关联

groups = session.query(Group).all()   　　　　　　　　　　　　　　　　　　 #找出所有的组
h1 = session.query(Host).filter(Host.hostname=='nginx').first()　　　　　　#将h1找到
h1.group = groups　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　  #将h1关联找到的所有的组　　
#看颜色对照

查询已经关联

mysql> select * from host_2_group;
+---------+----------+
| host_id | group_id |
+---------+----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
+---------+----------+
4 rows in set (0.00 sec)

3、创建主机并且关联  （直接在创建主机的时候就关联也可以）
h1 = Host(hostname='nginx',ip_addr='172.40.0.175',port=50618)   #给Host增加一条数据
    h2 = Host(hostname='mysql',ip_addr='172.40.0.176')               #给Host增加一条数据
    h3 = Host(hostname='java',ip_addr='172.50.0.177',port=50617)    #给Host增加一条数据
    session.add_all([h1,h2,h3]) #注册全部创建
    groups = session.query(Group).all()
    h1.group = groups
    session.commit()

详细文档猛戳这里下载

二、 mysql

mysq 中创建一个数据库就是一个文件夹

1、创建数据库

# utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
  
# gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

2、创建用户

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
修改密码
    set password for '用户名'@'IP地址' = Password('新密码')
   
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

基本操作
3、创建表

create table 表名(
    列名  类型  是否可以为空，
    列名  类型  是否可以为空
)ENGINE=InnoDB DEFAULT CHARSET=utf8

是否可空，null表示空，非字符串
            not null    - 不可空
            null        - 可空

是否可以为空

 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
            create table tb1(
                nid int not null defalut 2,
                num int not null
            )

默认值

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null auto_increment,
                num int null,
                index(nid)
            )
            注意：1、对于自增列，必须是索引（含主键）。
                 2、对于自增可以设置步长和起始值
                     show session variables like 'auto_inc%';
                     set session auto_increment_increment=2;
                     set session auto_increment_offset=10;

                     shwo global  variables like 'auto_inc%';
                     set global auto_increment_increment=2;
                     set global auto_increment_offset=10;

自增

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
            create table tb1(
                nid int not null auto_increment primary key,
                num int null
            )
            或
            create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num)
            )

主键，相当于新华字典的目录

外键，一个特殊的索引，只能是指定内容
            creat table color(
                nid int not null primary key,
                name char(16) not null
            )

            create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )

外键

创建数据库的时候列数是定死的，不可能有动态的列,

设计表

服务器与业务表

一对多，

多对多

操作数据库（增删改查）

1、增：

1 2	`insert into 表 (列名,列名...) values (值,值,值...)insert into t2 (name,age) VALUES ('tian',18)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)<br>insert into t2 (name,age) VALUES ('tian',18),('xu',21)<br>` `insert into 表 (列名,列名...) select (列名,列名...)` `from` `表`

2、删：

1 2	`delete` `from` `表` `delete` `from` `表 where` `id＝1` `and` `name＝'alex'`

3、改

1	`update 表` `set` `name ＝` `'alex'` `where` `id>1`

4、查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

5、其他

a、条件
    select * from 表 where id > 1 and name != 'alex' and num = 12;
  
    select * from 表 where id between 5 and 16;
  
    select * from 表 where id in (11,22,33)
    select * from 表 where id not in (11,22,33)
    select * from 表 where id in (select nid from 表)
  
b、通配符
    select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
    select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）
  
c、限制
    select * from 表 limit 5;            - 前5行
    select * from 表 limit 4,5;          - 从第4行开始的5行
    select * from 表 limit 5 offset 4    - 从第4行开始的5行
  
d、排序
    select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
    select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
    select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
  
e、分组
    select num from 表 group by num
    select num,nid from 表 group by num,nid
    select num,nid from 表  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
  
    select num from 表 group by num having max(id) > 10
  
    特别的：group by 必须在where之后，order by之前 
f、连表
    显示所有的表。
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
　　 <br><br>
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid    select * from t10 inner join t11 on t10.type_id = t11.id<br>　　 将t10 和t11表链接显示， 链接条件： （inner join，无对应关系的不显示） t10 的type_id 和t11 的id相等 <br>
    A表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid<br>    <br>    select * from t10 left join t11 on t10.type_id = t11.id<br>　　 以 left join 前面的t10表为主， 后边的表无关系的表不显示，如果前面的表有无对应关系的就显示null　　　<br>
  
    B表所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid<br>    和 left join 相反
 <br>
g、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B<br><br><br>
  
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B<br><br>

组合：

union

把t10 和t11表中的所有的数据都放到一张表中，并且如果有重复的就去重。

union all

把t10 和t11表中的所有的数据都放到一张表中，不去重。

分组：

1 2	`select` `sum(价格),资产类型，` `from` `t1 group by 资产类型` `显示价格总和，资产类型，从t1中查找，按照资产类型进行分组（分组就是把group by后面资产类型为1的分一组，资产为2的分一组）`

posted @ 2016-07-28 22:59 wawahan 阅读(425) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 《HelloGitHub》第 108 期
· Windows桌面应用自动更新解决方案SharpUpdater5发布
· 我的家庭实验室服务器集群硬件清单
· C# 13 中的新增功能实操
· Supergateway：MCP服务器的远程调试与集成工具

公告

昵称： wawahan
园龄： 8年10个月
粉丝： 4
关注： 1

2025年3月

日

一

二

三

四

五

六

wawahan

python_way day12 sqlalchemy，原生mysql命令

python_way day12 sqlalchemy，mysql原生命令

一，sqlalchemy

二、 mysql

公告

搜索

常用链接

随笔档案

阅读排行榜

推荐排行榜