Tutorial on installing and using Python popular ORM framework sqlalchemy

This article describes the installation and use of the popular Python ORM framework sqlalchemy. Share it for your reference, as follows:

Install

1. Installation

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

import sqlalchemy
print(sqlalchemy.__version__) # 1.1.15

The version I'm using here is 1.1.15

Create a connection object

/en/latest/orm/#connecting

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

An error occurs during runtime because the driver library is required, MySQLdb will be called by default.

ImportError: No module named 'MySQLdb'

We installed pymysql before, so we need to write the complete one like this:

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

Simple use

SQL statement query

result = ("select * from news")
print(())
#[(1, 'Native News Title'), (2, 'Today'), (3, 'News Title'), (4, 'News Title 2'), (5, 'Tuote News 1'), (6, 'Tuote News 2')]

Create a map

Since we use ORM, we just want to write less or even less SQL statements.

ORM is a mapping between a data table and an object.

/en/latest/orm/#declare-a-mapping

1. Create a file, let’s map the data table.

from  import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String
class News(Base):
  # Table name  __tablename__ = 'news'
  # News table id field  id = Column(Integer, primary_key=True, autoincrement=True)
  # title field in the news table  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  import News
from  import sessionmaker
# Connect to the local test databaseengine = create_engine("mysql+pymysql://root:root@localhost/test?charset=utf8")
# Create a sessionsession = sessionmaker(engine)
mySession = session()
# Query result setresult = (News).all()
print(result[0])

We need to pay attention to the last query result and see what the elements in the result set look like? ^_^

< object at 0x1050c6e80>

The records processed by the query are all objects.

Various inquiries

Query only the first record

# Query the first itemresult = (News).first()
print() #Print object properties

Query via id field

# Query id 2result = (News).filter_by(id=2).first()
print()

# Query id 2result = (News).filter(==2).first()

Pagination query

# Pagination query 0,2result = (News).filter(&gt;1).limit(2).offset(0).all()
print(result)

Custom filtering conditions

# Custom filtering conditionsresult = (News).filter(text("id&gt;:id")).params(id=2).all()

Query by primary key

result = (News).get(3)
print()

Add and modify

# Addednews = News(title="Added test title")
(news)
()

#Revise(News).filter(==7).update({"title":"Modified title"})
()

For more information about Python-related content, please check out the topic of this site:Summary of common Python database operation skills》、《Summary of Python mathematical operation skills》、《Python data structure and algorithm tutorial》、《Summary of Python function usage tips》、《Summary of Python string operation skills》、《Python introduction and advanced classic tutorials"and"Summary of Python file and directory operation skills》

I hope this article will be helpful to everyone's Python programming.