ORM-mysql-Logger小记

ORM数据库的使用

orm英文全称object relational mapping,就是对象映射关系程序，简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

sqlalchemy（未有数据表）

常用的类

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker
# 数据库授权
GRANT ALL PRIVILEGES ON *.* TO 'fxq'@'%' IDENTIFIED BY ‘123456’;

常用的数据库连接

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>
      。e'x't
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
      
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

执行sql前的准备工作：轮子

# 创建连接
engine = create_engine("mysql+pymysql://root:xxxx@localhost/demo1",encode='utf-8',echo=True)
# 生成orm基类
base = declarative_base()
class User(base):
    _tablename__ = 'users' #表名
    id = Column(Integer, primary_key=True)
    name = Column(String(32)) #String->varchar
    password = Column(String(64))
# 创建与数据的会话class，这里不是实例，是类
Session_class = sessionmaker(bind=engine)
# 创建实例
session = Session_class()
user1 = User(id=1001, name='lili',password='123')
user2 = User(id=1002, name='didi', passord='456')
session.add_all([user1,user2])
session.commit()
session.close()

常用sql操作（创建表，添加数据，查询数据，修改）

创建表

Base.metadata.creat_all(engine)

添加数据

add_all可一次性添加多条数据

user_obj = User(name='cc',password='123123' session.add(user_obj) session.commit()

查询

my_user = session.query(User).filter_by(name='cc').first()

def __repr__(self):
    return "<User(name='%s',password='%s')>" % (self.name, self.password)
# 获取所有数据
print(session.query(user.id, user.name)).all()
# 多条件查询
objs = session.query(user).filter(user.id>0).filter(user.id<=3).all()
# 统计和分组
objs = session.query(user).filter(user.name.like('r%')).count()
from sqlalchemy import func
objs = session.query(func.count(user.name),user.name).group_by(user.name).all()

删除数据

person = session.query(Person).first() session.delete(person) session.commit()

sqlalchemy(已有相关表)

轮子

from sqlalchemy import create_engine, MeraData
from sqlalchemy import Table
# 建立链接
engine = create_engine("mysql+pymysql://root:123456@localhost/demo1",
                       encoding='utf-8', echo=False)
# 建立引擎
metadata = MetaData(bind=engine)
# 建立表对象（使用引擎）
Table(tablename,metadata,autoload=False)
# 建立事务 连接绑定
session = Session(bind=engine)

新增

session.execute(table.insert(),info) -> table.insert().values(info)

删除

session.execute(table.delete().where(table.c.Tag==value)) -> delete from tablename where col_name==value

修改

table.update().where(table.c.Tag==value).values(info) -> update tablename set field=new_value where col_name==value

查询

select = table.select(table.c.Tag==value) session.execute(select).fetchall()

table.select(table.c.USER_NAME=='admin').order_by(table.c.ID.desc())

一套简单的组合拳

engine = create_engine("mysql+pymysql://root:root@localhost/demo1",
                       encoding='utf-8', echo=False)
class Tool():

    @classmethod
    def get_session(cls):
        session = Session(bind=engine)
        return session

    @classmethod
    def get_table(cls, tablename):
        # 绑定引擎
        metadata = MetaData(bind=engine)
        return Table(tablename, metadata, autoload=True)

    @classmethod
    def add_data_session(cls, session, table, info):
        print(info)
        if ('sql' in str(type(table))) and hasattr(info, '__iter__'):
            session.execute(table.insert(), info)
            session.commit()
        else:
            raise Exception('add error')

    @classmethod
    def del_data_session(cls, session, table, col_name, username):
        if 'sql' in str(type(table)):
            session.execute(table.delete().where(getattr(table.c, col_name) == username))
            session.commit()
        else:
            raise Exception('del error')

    @classmethod
    def check_exist(cls, session, table, col_name, username):
        select = table.select(getattr(table.c, col_name) == username)
        result = session.execute(select).fetchall()
        if len(result) != 0:
            return True
        else:
            return False

    @classmethod
    def query_info(cls, session, table, col_name, username):
        select = table.select(getattr(table.c, col_name) == username)
        result = session.execute(select).fetchall()
        return result

    @classmethod
    def modify_info(cls, session, table, col_name, username, info):
        _var = table.update().where(getattr(table.c, col_name) == username)
        session.execute(_var.values(), info)
        pass

    @classmethod
    def exceptionSo(cls, func):
        import functools
        @functools.wraps(func)
        def inner(*args):
            print('1')
            try:
                func(*args)
            except Exception as e:
                print("your input is wrong")
            finally:
                print('------------')

        return inner

Logger的使用（原理不明）

使用sys库将控制台输出至log文件中

import sys
class Logger():
    def __init__(self, filename='default.log',stream=sys.stdout):
        self.terminal = stream
        self.log = open(filename, 'a+')
    def write(self,message):
        self.terminal.write(message)
        self.log.write(message)
    def flush(self):
        pass
sys.stdout = Logger('a.log',sys.stdout)
sys.stderr = Logger('a.log',sys.stderr)