Environment: Python 3.7, pycharm

ORM(Object-Relational-Mapper)

Object Relational Mapping (ORM) is a technology that allows you to query and manipulate data from a database using object-oriented paradigms. sqlalchemy is one of the libraries (frameworks) that implement ORM technology.

advantage

• Simplify development because it automatically performs object-to-table and table-to-object transformations, thereby reducing development and maintenance costs
• Less code than embedded SQL and handwritten stored procedures
• Transparent object caching in application layer improves system performance
• Optimized solutions make applications faster and easier to maintain

mapping type

sqlalchemy operation

1. import

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base

2. Linking database

db = sqlalchemy.create_engine("mysql+pymysql://root:11111111@localhost/demo")

3. Create an inheritance base class

base = declarative_base(db)

4. mapping

class User(base):
    __tablename__ = 'student'

id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(32))

5. create table

if __name__ == '__main__':

base.metadata.create_all(db)

6. Operating database

1.Import
from sqlalchemy.orm import sessionmaker

2.Binding a query instance
session = sessionmaker(bind=db)
session = session()

3.insert 
######Insert single data
user =User(
    id = 4,
    name='python'
)
session.add(user)
session.commit()

######Insert multiple data
session.add_all(
   [
       User(id=5,name='java'),
       User(id=6,name='php')
   ]
 )
session.commit()

4.query
######Query multiple data
data = session.query(User).all()
for x in data:
    print (x.name)

data = session.query(User).filter(User.name== 'xxxx').all()
######Query single data
data = session.query(User).filter(User.name == 'xxxx').first()
data = session.query(User).filter_by(name='xxxx').first()
######Use get to query id
data = session.query(User).get(ident = 10)
print (data.name)

5.modify
data = session.query(User).get(10)
print (data)
data.name = 'laoli'
session.merge(data)
session.commit()
//perhaps
session.query(User).filter(User.id == 10).update({User.name:"xxxxx",User.id:11})
session.commit()

6.delete
data = session.query(User).filter(User.id == 11).first()
session.delete(data)
session.commit()