Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to use Python's ORM framework sqlalchemy". The editor shows you the operation process through an actual case. The method of operation is simple, fast and practical. I hope this article "how to use Python's ORM framework sqlalchemy" can help you solve the problem.

Installation

1. Installation

# enter the virtual environment # execute. / python3-m pip installimport sqlalchemyprint (sqlalchemy.__version__) # 1.1.15 the version I use here is 1.1.15 to create a connection object from sqlalchemy import create_engine# to connect to the local test database engine = create_engine ("mysql://root:root@localhost/test?charset=utf8")

Errors occur at run time because the library needs to be driven and MySQLdb is called by default.

ImportError: No module named "MySQLdb"

We installed pymysql earlier, so write it in its entirety:

Engine = create_engine ("mysql+pymysql://root:root@localhost/test?charset=utf8") is easy to 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 mappings

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

ORM is the mapping between data tables and objects.

1. Create an Infos.py file, which we will map to the data table.

From sqlalchemy.ext.declarative import declarative_baseBase = declarative_base () from sqlalchemy import Column, Integer, Stringclass News (Base): # Table name _ _ tablename__ = "news" # id field id = Column (Integer, primary_key=True, autoincrement=True) # title field title = Column (String (length=255), nullable=False) in news table

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

2. Use

From sqlalchemy import create_enginefrom mappers.Infos import Newsfrom sqlalchemy.orm import sessionmaker# connects to the local test database engine = create_engine ("mysql+pymysql://root:root@localhost/test?charset=utf8") # create 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 result to see what the elements in the result set look like. ^ _ ^

The records processed by the query are all objects.

Various inquiries

Query only the first record

# query the first result = mySession.query (News). First () print (result.title) # print object attributes through the id field query # query result = mySession.query (News). Filter_by (id=2). First () print (result.title) # query result = mySession.query (News) .filter (News.id==2). First () paging query # paging query 0result = mySession.query (News). Filter (News.id > 1) .limit (2) .offset (0). All () print (result) Custom filter condition # Custom filter condition result = mySession.query (News) .filter (text ("id >: id")) .params (id=2) .all ()

Query according to the primary key

Result = mySession.query (News). Get (3) print (result.title) add and modify # 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 called Infos.py, and then defined a News class as a mapping to our news data table.

From sqlalchemy.ext.declarative import declarative_baseBase = declarative_base () from sqlalchemy import Column, Integer, Stringclass News (Base): # Table name _ _ tablename__ = "news" # id field id = Column (Integer, primary_key=True, autoincrement=True) # title field title = Column (String (length=255), nullable=False) in news table

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

1. Install sqlacodegen

# cd project virtual environment # execute. / python3-m pip install sqlacodegen

2. Use sqlacodegen to generate case columns

# Note: execute. / sqlacodegen-- tables fund-- outfile. /.. / mappers/Found.py mysql+pymysql://root:root@localhost/test?charset=utf8 in the virtual environment directory. This is the end of the introduction of "how to use Python's ORM framework sqlalchemy". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.