Python Popular ORM Framework sqlalchemy Simple Use

Installation

http://docs.sqlalchemy.org

1. Installation

# Enter the virtual environment 
# Execute 
./python3 -m pip install

import sqlalchemy
print(sqlalchemy.__version__) # 1.1.15
 The version I use here is 1.1.15

Create a connection object

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#connecting

from sqlalchemy import create_engine
#  Connect local test Database 
engine = create_engine("mysql://root:root@localhost/test?charset=utf8")

An error occurs at runtime, because the driver library is required, and MySQLdb is called by default.

ImportError: No module named 'MySQLdb'

We installed pymysql earlier, so we should write it completely as follows:

engine = create_engine("mysql+pymysql://root:root@localhost/test?charset=utf8")

Simple use

SQL statement query

result = engine.execute("select * from news")
print(result.fetchall())
#[(1, ' Local news headlines '), (2, ' Today's news '), (3, ' News headlines 1'), (4, ' News headlines 2'), (5, ' Tuple news 1'), (6, ' Tuple news 2')]

Create a map

Since we use ORM, it is to write fewer or no SQL statements.

ORM is a mapping between data tables and objects.

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html#declare-a-mapping

1. Create an Infos. py file, which we will do the mapping of data table

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class News(Base):
#  Table name 
__tablename__ = 'news'
# news Exterior and interior id Field 
id = Column(Integer, primary_key=True, autoincrement=True)
# news Exterior and interior title Field 
title = Column(String(length=255), nullable=False)

The News class is the mapping of our data table news (fields: id, title).

2. Use

from sqlalchemy import create_engine
from mappers.Infos import News
from sqlalchemy.orm import sessionmaker
#  Connect local test Database 
engine = create_engine("mysql+pymysql://root:root@localhost/test?charset=utf8")
#  Create a session 
session = sessionmaker(engine)
mySession = session()
#  Query result set 
result = mySession.query(News).all()
print(result[0])

We should pay attention to the final query results and see what the elements in the result set look like. ^ _ ^

< mappers.Infos.News object at 0x1050c6e80 >

The records processed by the query are all objects.

Various queries

Only query the first record

#  Enquiry number 1 Article 
result = mySession.query(News).first()
print(result.title) # Print object properties 
 Pass id Field query 

#  Query id For 2 Adj. 
result = mySession.query(News).filter_by(id=2).first()
print(result.title)
#  Query id For 2 Adj. 
result = mySession.query(News).filter(News.id==2).first()
 Paged query 

#  Paged query  0,2
result = mySession.query(News).filter(News.id>1).limit(2).offset(0).all()
print(result)
 Custom filter criteria 

#  Custom filter criteria 
result = mySession.query(News).filter(text("id>:id")).params(id=2).all()

Query by primary key

result = mySession.query(News).get(3)
print(result.title)

Addition and Modification

#  Add 
news = News(title=" Add Test Title ")
mySession.add(news)
mySession.commit()
# Modify 
mySession.query(News).filter(News.id==7).update({"title":" Modified title "})
mySession.commit()

Python automatically generates ORM entity class examples using sqlacodegen

In the previous method, we manually created a file named Infos. py, and then defined an News class as a mapping with our news data table.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class News(Base):
#  Table name 
__tablename__ = 'news'
# news Exterior and interior id Field 
id = Column(Integer, primary_key=True, autoincrement=True)
# news Exterior and interior title Field 
title = Column(String(length=255), nullable=False)

Now let's take a look at sqlacodegen, a tool that automatically generates class files like the one above.

1. Install sqlacodegen

from sqlalchemy import create_engine
#  Connect local test Database 
engine = create_engine("mysql://root:root@localhost/test?charset=utf8")

0

2. Use sqlacodegen to generate case columns

from sqlalchemy import create_engine
#  Connect local test Database 
engine = create_engine("mysql://root:root@localhost/test?charset=utf8")

1