#encoding: utf-8

from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

from random import randint

HOSTNAME = '127.0.0.1'

PORT = 3306

DATABASE = 'first_sqlalchemy'

USERNAME = 'root'

PASSWORD = ''

#dialect+driver://username:password@host:port/database

DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/" \

         "{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

engine = create_engine(DB_URI)

Base = declarative_base(engine)

# Session = sessionmaker(engine)

# session = Session()

session = sessionmaker(engine)() #Session(**local_kw)

class Article(Base):

    __tablename__  = 'article'

    id = Column(Integer,primary_key=True,autoincrement=True)

    title = Column(String(50),nullable=False)

    price = Column(Float,nullable=False)

    content = Column(String(100))

    def __repr__(self):

        return '<Article(title:%s)>'%self.title

# Base.metadata.drop_all()

#

# Base.metadata.create_all()

#

# for x in range(6):

#     article = Article(title='title%s'%x,price=randint(0,100))

#     session.add(article)

# session.commit()

# 1、equal 等于的意思

article = session.query(Article).filter(Article.id == 1).first()

article_title = session.query(Article).filter(Article.title == 'title1').first()

print(article)

print(article_title)

# 2、not equal 不等于的意思

article_title_not_equal = session.query(Article).filter(Article.title != 'title0').all()

print(article_title_not_equal)

#3、like

#注意：如果不查询，直接打印显示原生sql，不用first（）或 all()方法

article_title_like = session.query(Article).filter(Article.title.like('title%') ).all()

#相当于sql    select * from article where title like '%title%';

print(article_title_like)

#插入一条数据的sql ：insert into article values(null,'abc','100');

# 4、ilike(不区分大小写)

article_title_ilike = session.query(Article).filter(Article.title.ilike('title%') )

print(article_title_ilike)

#SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price FROM article

# WHERE lower(article.title) LIKE lower(%(title_1)s)

# 5、in (在某某里面)

#为什么用in_，因为要避开关键字in

article_title_in = session.query(Article).filter(Article.title.in_(['title1','title2'])).all()

print(article_title_in)

#原生sql SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price

# FROM article

# WHERE article.title IN (%(title_1)s, %(title_2)s)%（'title1','title2'），这是格式化字符串，防止sql注入

#6、not in (不在某某里面)

article_title_not_in = session.query(Article).filter(Article.title.notin_(['title1','title2'])).all()

print(article_title_not_in)

#not in (另一种写法) ~取反的意思

article_title_not_in_1 = session.query(Article).filter(~Article.title.in_(['title1','title2'])).all()

print(article_title_not_in_1)

# 7、is null 判断某个字段是否为空

# alter table article add column content text; 添加一列，

# 修改原先好的类别  alter table article modify column content varchar(100);

# update article set content='python or go' where id =7; 修改一条记录

is_null_content = session.query(Article).filter(Article.content == None).all()

print(is_null_content)

# not is null 不为空

not_is_null_content = session.query(Article).filter(Article.content != None)

print(not_is_null_content)

'''

SELECT article.id AS article_id, article.title AS article_title, article.price AS article_price,

article.content AS article_content

FROM article

WHERE article.content IS NOT NULL

'''

#8、and 和

title_and_content = session.query(Article).\

    filter(Article.title == 'python or go',Article.content == 'python or go').all()

print(title_and_content)

#另一种显示方式

title_and_content_1 = session.query(Article).\

    filter(and_(Article.title == 'python or go',Article.content == 'python or go'))

print(title_and_content_1)

#原生sql显示SELECT article.id AS article_id, article.title AS article_title,

#  article.price AS article_price, article.content AS article_content

# FROM article

# WHERE article.title = %(title_1)s AND article.content = %(content_1)s

#9.or

title_or_content = session.query(Article).filter\

    (or_(Article.title == 'python or go',Article.content == 'python or go')).all()

print(title_or_content)