1. Common Column parameters

1. primary_key: True sets a field as the primary key.

2. autoincrement: True sets this field to grow automatically.

3. Default: Sets the default value for a field. These fields are often used in publishing time.

4. nullable: Specifies whether a field is empty. The default value is True, which means it can be null.

5. Unique: specifies whether the value of a field is unique. The default is False.

6. Onupdate: The value or function specified by this parameter is called when the data is updated. When this data is first inserted, the onupdate value is not used, but the default value is used. It is often used in the update_time field (which is updated every time data is updated).

7. Name: Specifies the field name that an attribute in the ORM model maps to a table. If not specified, the name of this property is used as the field name. If specified, the specified value is used as the table field name. This parameter can also be used as a position parameter, which is specified in the first parameter.

   title = Column(String(50),name='title',nullable=False)
   
   title = Column('my_title',String(50),nullable=False)
   

• Code demonstration:

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import date,datetime,time
# enum module in python 3.x
import  enum

# Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

# dialect+driver://username:password@host:port/database?charset=utf8
# Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()
# Requirement: What are the commonly used parameters in Column in sqlalchemy?
class News(Base):
    __tablename__="news"
    id=Column(Integer,primary_key=True,autoincrement=True)
    create_time = Column(DateTime,default=datetime.now)
    read_count = Column(Integer,default = 1000)
    title =Column(String(50),nullable=False)
    telephone =Column(String(50),unique=True,nullable=False)
    # title1 =Column(String(50),name="my_title1")  #Write 1: ORM class attributes and table field names do not want to be the same using the name parameter to specify
    title1 = Column("my_title2",String(50))  #Write 2: ORM class attributes and table field names do not want to be the same using name parameters to specify
    update_time  = Column(DateTime,onupdate=datetime.now) # onupdate works only when data is updated, but it doesn't work when data is inserted.

# Base.metadata.drop_all()
# Base.metadata.create_all()

# Add data for testing
# news1 = News(title = "haha", telephone="18798224283")
# session.add(news1)
# news2 = News(title = "haha", telephone="18798224284")
# session.add(news2)
# session.commit()

# Modify the data to test the usage of onupdate parameters
news = session.query(News).first()
news.title= "dddddd"
session.commit()

2. query query function use

There are three kinds of parameters that query function can transfer:

1. Model name. Specify to find all attributes in this model (corresponding query table is full table query).

2. Attributes in the model. You can specify that only a few of the attributes of a model can be found.

3. Aggregation function:

* func.count: Number of rows counted.

* func.avg: Find the average.

* func.max: Find the maximum.

* func.min: Find the minimum.

* func.sum: sum.

There are no aggregation functions on func. But because he did some magic at the bottom, as long as there are aggregation functions in mysql, they can be called through func.

• The code is illustrated as follows:

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import date,datetime,time
# enum module in python 3.x
import enum
import random

# Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

#dialect+driver://username:password@host:port/database?charset=utf8
#Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()
# Requirement: What parameters can query query function pass in sqlalchemy?
class News(Base):
    __tablename__='news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title =Column(String(50),nullable=False)
    price = Column(Integer)

    def  __repr__(self):
        return "<title:%s,price:%s>"%(self.title,self.price)
# Base.metadata.drop_all()
# Base.metadata.create_all()
# New test data
# for x in range(1,6):
#     a = News(title = "title% s"%x,price =random.randint(1,100))
#     session.add(a)
# session.commit()


			##### query()What parameters can be written in a function: 3 #####
# 01. Model name
# results = session.query(News).all()  #Full Table Query
# print(results)

# 02. Attributes in the model name. The elements in the returned list are tuple type data
# results = session.query(News.title,News.price).all()
# print(results)

# 03.mysql aggregation function
r = session.query(func.count(News.id)).first()
print(r)

r = session.query(func.max(News.price)).first()
print(r)

r = session.query(func.min(News.price)).first()
print(r)

r = session.query(func.avg(News.price)).first()
print(r)

r = session.query(func.sum(News.price)).first()
print(r)

3. Common filter conditions of filter method

filter filtering conditions:

Filtering is a very important function of data extraction. The following is an explanation of some commonly used filtering conditions, which can only be achieved by filter method:

1. equals : ==

   news= session.query(News).filter(News.title == "title1").first()

2. not equals : !=

   query(User).filter(User.name != 'ed')

3. like (case-insensitive) & Ilike (case-insensitive)

   query(User).filter(User.name.like('%ed%'))

4. in:

   query(User).filter(User.name.in_(['ed','wendy','jack']))

5. not in:

   query(User).filter(~User.name.in_(['ed','wendy','jack']))

6. is null:

   1. query(User).filter(User.name==None)
   2. Or: query (User). filter (User. name. is (None))

7. is not null:

   1. query(User).filter(User.name != None)

   2. Or: query(User).filter(User.name.isnot(None))

8. and:

   1. query(User).filter(and_(User.name=='ed',User.fullname=='Ed Jones'))

      1. Or pass multiple parameters: query (User). filter (User. name ='ed', User. fullname =='Ed Jones')
         3. Or through multiple filter operations: query (User). filter (User. name ='ed'). filter (User. fullname ='Ed Jones')

9. or:

   query(User).filter(or_(User.name=='ed',User.name=='wendy'))

   If you want to look at the sql statement of the orm underlying transformation, you can see it after the filter method without executing any method to print directly. For example:

• Code example:

from sqlalchemy  import  create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func,and_,or_
from  sqlalchemy.ext.declarative  import  declarative_base
from sqlalchemy.dialects.mysql import  LONGTEXT
from sqlalchemy.orm  import  sessionmaker
from datetime import date,datetime,time
#enum module in python 3.x
import  enum
import random

#Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

#dialect+driver://username:password@host:port/database?charset=utf8
#Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

#Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()

class News(Base):
    __tablename__='news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title =Column(String(50),nullable=False)
    price = Column(Integer)
    content =Column(Text)

    def  __repr__(self):
        return  "<title:%s,price:%s,content:%s>"%(self.title,self.price,self.content)

# Base.metadata.drop_all()
# Base.metadata.create_all()
# #New test data
# for  x in range(1,6):
#     a = News(title = "title% s"%x,price =random.randint(1,100))
#     session.add(a)
# session.commit()

# Requirement: What are the filter function condition queries and commonly used filter conditions in sqlalchemy?
# A total of two query filtering methods
# r1 = session.query(News).filter(News.id == 1).first()
# print(r1)
# r2 =session.query(News).filter_by(id = 2).first()
# print(r2)

# 01. equal  ==
# news = session.query(News).filter(News.title = heading 1). first ()
# print(news)

# 02. not equal  !=
# news= session.query(News).filter(News.title!='title 1').all()
# print(news)

# 03. Like & Ilike (case-insensitive)
# news= session.query(News).filter(News.title.like('title%'). all()
# print(news)

# 04. in: 
# news= session.query(News).filter(News.title.in (['Title 1','Title 2']). all ()
# print(news)


# 05. not in
# news= session.query(News).filter(~News.title.in (['Title 1','Title 2']). all ()
# print(news)

# news= session.query(News).filter(News.title.notin (['Title 1','Title 2']). all ()
# print(news)

# 06. is null
# Modify the table, add a column content, and manipulate the data
# news= session.query(News).filter(News.content==None).all()
# print(news)

# 07. is not null
# news= session.query(News).filter(News.content!=None).all()
# print(news)

# 08. and
# news= session.query(News).filter(News.title ='heading 2',News.content.like('% dialogue%'). all()
# print(news)
# perhaps
# News = session. query (News). filter (and (News. title ='title 2',News.content.like('% dialogue%'). all()
# print(news)

# 09. or
news= session.query(News).filter(or_(News.title=='Title 3',News.content.like('%dialogue%'))).all()
print(news)

4. SQLALchemy Implementing Foreign Key and Its Four Constraints

Table Relations: There are three kinds of relationships among tables: one-to-one, one-to-many and many-to-many. ORM in SQLAlchemy can also simulate these three relationships. Because one-to-one is actually simulated in one-to-many way at the bottom of SQLAlchemy, let's first look at the next one-to-many relationship:

1. Foreign keys: Creating foreign keys using SQLAlchemy is very simple. Add a field from the table to specify which field of the table is the foreign key of the field. The fields of keys from a slave table must be consistent with the primary key field type of the main table.

   The sample code is as follows:

# Master/slave table
# user/news
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)

    def __repr__(self):
        return "<User(uname:%s)>" % self.uname

class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    uid = Column(Integer,ForeignKey("user.id"))

    def __repr__(self):
        return "<News(title:%s,content=%s)>" % (self.title,self.content)

1. Foreign key constraints have the following items:
   1. RESTRICT: Deleting the parent table's corresponding data will prevent deletion if the parent table has its associated data in the child table. If no deletion policy is specified, the default is RESTRICT.
   2. NO ACTION: In MySQL, with RESTRICT.
   3. CASCADE: Cascade deletion.
   4. SET NULL: The parent table corresponding data is deleted and the child table corresponding data item is set to NULL.

• Code demonstration:

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func,and_,or_,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker
from datetime import date,datetime,time
# enum module in python 3.x
import enum
import random

# Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

# dialect+driver://username:password@host:port/database?charset=utf8
# Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()

# Requirements: SQLALchemy Implementing Foreign Key and Its Four Constraints
# Master/slave table
# user/news
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)

    def __repr__(self):
        return "<User(uname:%s)>" % self.uname

class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    
    # The Method of Implementing Foreign Key in SQL ALchemy
    # uid = Column(Integer,ForeignKey("user.id"))  #The default deletion strategy is RESTRICT
    
    # Four Constraints [RESTRICT]
    # uid = Column(Integer,ForeignKey("user.id",ondelete="RESTRICT"))  #The default deletion strategy is RESTRICT
    # Four Constraints [NO ACTION]
    # uid = Column(Integer,ForeignKey("user.id",ondelete="NO ACTION"))  #The default deletion strategy is RESTRICT
    # Four Constraints [CASCADE]
    # uid = Column(Integer,ForeignKey("user.id",ondelete="CASCADE"))  #The default deletion strategy is RESTRICT
    # Four Constraints [SET NULL]
    uid = Column(Integer,ForeignKey("user.id",ondelete="SET NULL"))  # The default deletion strategy is RESTRICT

    def __repr__(self):
        return "<News(title:%s,content=%s)>" % (self.title,self.content)

# Create tables
Base.metadata.drop_all()
Base.metadata.create_all()

# Adding test data
user = User(uname='momo')
session.add(user)
session.commit()

news1= News(title='AAA',content='123',uid=1)
news2= News(title='BBB',content='456',uid=1)
session.add_all([news1,news2])
session.commit()

5. ORM-level foreign keys and one-to-many relationships

Mysql level foreign keys, not cool enough, you have to get a table of foreign keys, and then through this foreign key to another table to look up, which is too troublesome.

SQLAlchemy provides a relationship, which defines attributes and can be accessed directly by attributes when accessing associated tables in the future. In addition, you can specify the property name for reverse access by backref. newss refers to multiple news articles. Their relationship is a "one-to-many" relationship.

• Code example:

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func,and_,or_,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker,relationship
from datetime import date,datetime,time
# enum module in Python 3.x
import enum
import random

# Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

# dialect+driver://username:password@host:port/database?charset=utf8
# Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()

# Master/slave table
# user/news
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)

    # Add Attributes: Optimize Table 2 Query Operations
    # newss = relationship("News")  # This is not the best way to write it. Usually it is written on the "many" side by reverse declaration.

    def __repr__(self):
        return "<User(uname:%s)>" % self.uname

class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    # The Method of Implementing Foreign Key in SQL ALchemy
    uid = Column(Integer,ForeignKey("user.id"))  #The default deletion strategy is RESTRICT

    # Add Attributes; Optimize 2 Table Query Operations
    # Positive
    # author = relationship("User")
    # Ultimately: Write the [forward] and [reverse] relationships together; the reverse is to query the attributes of the `author'through `User'.
    author = relationship("User",backref="newss")

    def __repr__(self):
        return "<News(title:%s,content=%s)>" % (self.title,self.content)

# Create tables
# Base.metadata.drop_all()
# Base.metadata.create_all()

# #Adding test data
# user = User(uname='momo')
# session.add(user)
# session.commit()

# news1= News(title='AAA',content='123',uid=1)
# news2= News(title='BBB',content='456',uid=1)
# session.add_all([news1,news2])
# session.commit()

# Requirements: ORM-level foreign keys and one-to-many relationships

# Requirement 01: Authors of search articles
# news = session.query(News).first()
# print(news)
# print(news.uid) #Which id user wrote the news?
# user = session.query(User).get(news.uid)
# print(user)
# print(user.uname)

# CONCLUSION: The above code operations can meet the requirements, but the operation is too troublesome, so relationpship based on ORM model can be introduced to optimize (query function optimization).
news = session.query(News).first()
print(news)
print(news.author)
print(news.author.uname)

# Requirement 02: Query all the news of the xx author
user = session.query(User).first()
print(user.newss)

6. ORM-level foreign key and one-to-one relationship implementation

• One-to-one ORM relationship:

  In sqlalchemy, if you want to map two models into a one-to-one relationship, you should pass in a uselist=False parameter when specifying a reference in the parent model. That is to tell the parent model that when this slave model is referenced later, it is no longer a list, but an object. The sample code is as follows:

• Mode 01: (easy to understand)

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)
    extend = relationship("UserExtend",uselist=False) ##

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))
    user = relationship("User")  ##

• Mode 02: (More used)

  Of course, you can also use sqlalchemy.orm.backref to simplify the code:

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)

class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    uid = Column(Integer,ForeignKey("user.id"))
    # Recommendations are written in the slave table
    user = relationship("User",backref=backref("extend",uselist=False)) ##

• Code implementation:

from sqlalchemy  import  create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func,and_,or_,ForeignKey
from  sqlalchemy.ext.declarative  import  declarative_base
from sqlalchemy.dialects.mysql import  LONGTEXT
from sqlalchemy.orm  import  sessionmaker,relationship,backref
from datetime import date,datetime,time
#enum module in python 3.x
import  enum
import random

# Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

# dialect+driver://username:password@host:port/database?charset=utf8
# Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

# Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()

# Master/slave table
# user/news: 1: n
# user/user_extend: 1: 1

# Table 01
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    uname = Column(String(50),nullable=False)

    #Adding Attributes to Optimize Table 2 Query Operations
    # newss = relationship("News")   #This is not the best way to write it. Usually it is written on the "many" side by reverse declaration.

    # 1:1 Relation Representation 1
    # extend = relationship("UserExtend",uselist=False)

    def __repr__(self):
        return "<User(uname:%s)>" % self.uname

# Table 03
class UserExtend(Base):
    __tablename__ = 'user_extend'
    id = Column(Integer, primary_key=True, autoincrement=True)
    school = Column(String(50))
    #foreign key
    uid = Column(Integer,ForeignKey("user.id"))

    # 1:1 Relation Representation 01
    # user = relationship("User")
    # 1:1 Relation Representation 02
    user = relationship("User",backref = backref("extend",uselist=False))

# Table 02
class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    # The Method of Implementing Foreign Key in SQL ALchemy
    uid = Column(Integer,ForeignKey("user.id"))  # The default deletion strategy is RESTRICT

    # Adding Attributes to Optimize Table 2 Query Operations
    # Positive
    # author = relationship("User")
    # Ultimately: Write the positive and negative relationships together.
    author = relationship("User",backref="newss")

    def __repr__(self):
        return "<News(title:%s,content=%s)>" % (self.title,self.content)

# Create tables
# Base.metadata.drop_all()
# Base.metadata.create_all()

# Requirements: ORM-level foreign key and one-to-one relationship implementation
# Benefit 01: Add data; User adds User Extend
# user = User(uname= "Wang Wu")
# ux = UserExtend(school= "Nanjing University")
# user.extend = ux
# # print(type(user.extend))
# session.add(user)
# session.commit()

# Benefit 01: Add data; add User through UserExtend
# ux = UserExtend(school= "Wuhan University")
# user2 = User(uname = "Li Si")
# ux.user = user2
# print(type(ux.user))
# session.add(ux)
# session.commit()

# Benefit 02: Query data
user3 = session.query(User).first()
print(user3.uname)
print(user3.extend.school)

7. Implementation of foreign keys and many-to-many relationships at ORM level

• Steps:

1. Many-to-many relationships need to be bound by a ** middle table **.

2. Firstly, we define two models that need to do many-to-many.

3. Define an intermediate table using Table (from sqlalchemy import Table). The intermediate table is usually a foreign key field containing two models, and let them both act as a "composite primary key".

4. Choose a model randomly in two models that need to do many-to-many and define a relationship attribute to bind the relationship between the three. When using relationship, you need to pass in a secondary = middle table object name.

• Code demonstration:

from sqlalchemy import create_engine,Column,Integer,String,Float,DECIMAL,Boolean,Enum,Date,DateTime,Time,Text
from sqlalchemy import func,and_,or_,ForeignKey,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import sessionmaker,relationship,backref
from datetime import date,datetime,time
#enum module in python 3.x
import  enum
import random

#Prepare basic information for connecting to database
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'first_sqlalchemy'
USERNAME = 'root'
PASSWORD = 'root'

#dialect+driver://username:password@host:port/database?charset=utf8
#Organize database information according to the above format
DB_URI ="mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)

#Create a database engine
engine = create_engine(DB_URI)

Base = declarative_base(engine)
session = sessionmaker(engine)()

# Requirements: ORM-level foreign key and many-to-many relationship implementation n:n
#Table 3 Intermediate Table
news_tag = Table(
    "news_tag",
    Base.metadata,
    Column("news_id",Integer,ForeignKey("news.id"),primary_key=True),
    Column("tag_id",Integer,ForeignKey("tag.id"),primary_key=True)
)

#Table 1
class News(Base):
    __tablename__ = 'news'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)

    # Generative Relation Writing 1
    # tags = relationship("Tag",backref="newss",secondary=news_tag)
    def __repr__(self):
        return "<News(title:%s)>" % self.title

#Table 2
class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)

    # Generative Relation Writing 2
    newss = relationship("News",backref="tags",secondary=news_tag)
    def __repr__(self):
        return "<Tag(name:%s)>" % self.name

# 1. Define two models that need to do many-to-many first.
# 2. Define an intermediate table with Table. The intermediate table is usually a foreign key field containing two models, and let the two of them act as a "composite primary key".
# 3. Choose a model randomly and define a relationship attribute to bind the relationship among the two models that need to do many-to-many.
# 4. When using relationship, you need to pass in a secondary = middle table object name.

# Base.metadata.drop_all()
# Base.metadata.create_all()

# Benefits of adding data
# news1 = News(title = world number one)
# news2 = News(title = "second in the world")

# tag1 = Tag(name ='news')
# tag2 = Tag(name ='entertainment')

# news1.tags.append(tag1)
# news1.tags.append(tag2)
#
# news2.tags.append(tag1)
# news2.tags.append(tag2)

# session.add(news1)
# session.add(news2)

# session.commit()

# Benefits of querying data (often used)
news3 = session.query(News).first()
print(news3.tags)

tag = session.query(Tag).first()
print(tag.newss)