Basic operations of Sqlalchemy connecting to MySQL database

Time:2022-5-7

sqlalchemy_test.py

#!/usr/bin/env python3.8.6
# _*_ coding: utf-8 _*_
# Description:
# Author: qiaoxiaohang 
# Date: 2022/4/8




from config import DB_URI
from sqlalchemy import create_engine


#Todo creates the engine and links the database
# engine = create_ Engine (db_uri) # create engine
# conn = engine. Connect() # link
# result = conn.execute('select * from boo')
# print(result.fetchone())
#Conn.close() # close the link


#Todo creates the ORM model and maps it to the database

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine,Column,Integer,String
from sqlalchemy.orm import sessionmaker

engine = create_ Engine (db_uri) # build engine
Base = declarative_ Base (engine) # create SQL ROM base class
Session = sessionmaker (engine) # build the session object

#Todo create database table
'''
Create database table student
class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))

Base. metadata. create_ All() # maps the model to the database
'''
#Todo new data
class Student(Base):
    __tablename__ = "student"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(50))
    age = Column(Integer)
    sex = Column(String(10))

'''
student = Student(name='Tony',age = 16,sex = 'male')
session. Add (student) # single data insertion
session.commit()
-----------------------------------------------------
session.add_all([
    Student(name='Jane', age=16, sex='female'),
    Student(name='Ben', age=20, sex='male')
]) # batch insert data
session.commit()
'''

#Todo query data -- Sqlalchemy provides a query () method to query data
data_list = session.query(Student).all()
print(data_list)
for data in data_list:
    print(data.name,data.age,data.sex)
#Output:
# [, , ]
# Tony 16 male
# Jane 16 female
# Ben 20 male
#Todo specify query
name_list = session.query(Student.name).all()
# name_ list = session. query(Student.name). First() # get the first row of returned data
print(name_list)
#Output: [('tony ',), ('Jane',), ('ben ',)]
#Todo uses the filter () method to filter
filter_list = session.query(Student.name).filter(Student.age>=18).all()
print(filter_list)#Output:[('Tony',), ('Ben',)]

#Todo uses order_ Sort by()

item_ list = session. query(Student.name, Student.age). order_ by(Student.age.desc()). All() # desc() indicates reverse order
print(item_list)

# [('Ben', 20), ('Tony', 18), ('Jane', 16)]

#Todo multiple query criteria (and and or)
#The default is and. In filter(), it is used to separate multiple conditions to represent and
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age >= 10, Student.sex == 'female'
).all()
print(item_list)  # [('Jane', 16, 'female')]


from sqlalchemy import or_

#Use or_ Connect multiple conditions
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    or_(Student.age >= 20, Student.sex == 'female')
).all()
print(item_list)  # [('Jane', 16, 'female'), ('Ben', 20, 'male')]


# todo equal/like/in
#Equals
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age == 18
).all()
print(item_list)  # [('Tony', 18, 'male')]

#Not equal to
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age != 18
).all()
print(item_list)  # [('Jane', 16, 'female'), ('Ben', 20, 'male')]

# like
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.name.like('%To%')
).all()
print(item_list)  # [('Tony', 18, 'male')]

# in
item_list = session.query(Student.name, Student.age, Student.sex).filter(
    Student.age.in_([16, 20])
).all()
print(item_list) # [('Jane', 16, 'female'), ('Ben', 20, 'male')]


#Todo count count
count = session.query(Student).count()
print(count)  # 3


#Todo slice
item_list = session.query(Student.name).all()[:2]
print(item_list)  # [('Tony',), ('Jane',)]

#Todo modify data
#You can use the update () method to modify the data. Remember to execute the session after the update is completed commit()
#Modify Tony's age to 22
session.query(Student).filter(Student.name == 'Tony').update({'age': 22})
session.commit()

item = session.query(Student.name, Student.age).filter(Student.name == 'Tony').first()
print(item) #('Tony', 22)

#Todo delete data
#To delete data, use the delete () method, which also needs to execute session Commit() commit transaction
#Delete data named Ben
session.query(Student).filter(Student.name == 'Ben').delete()
session.commit()

item_list = session.query(Student.name, Student.age).all()
print(item_list)#[('Tony', 22), ('Jane', 16)]

Database configuration parameters

config.py

#!/usr/bin/env python3.8.6
# _*_ coding: utf-8 _*_
# Description:
# Author: qiaoxiaohang 
# Date: 2022/4/8
HOST = 'localhost'
PORT = 3306
USERNAME = 'root'
PASSWORD = '123456'
DB = 'test'
DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'

Recommend a good website: https://www.jb51.net/article/173950.htm

Recommended Today

Chapter 45 SQL command from (I)

Chapter 45 SQL command from (I) A select clause that specifies one or more tables to query. outline SELECT … FROM [optimize-option] table-ref [[AS] t-alias][,table-ref [[AS] t-alias]][,…] parameter optimize-optioN – optional – specifies a single keyword or a series of keywords separated by spaces for query optimization options (optimizer tips). The following keywords are supported:%ALLINDEX、%FIRSTTABLE […]