创建模型
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None
创建表
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
添加单条数据
from tkinter. font import namesimport sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs = Hero( name= "张三" , real_name= "张三" , age= 33 ) session. add( zs) session. commit( )
添加多条数据
from tkinter. font import namesimport sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) session. commit( )
根据名称查询数据
from tkinter. font import namesimport sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) query = sqlmodel. select( Hero) . where( Hero. name== "张三1" ) hero = session. exec ( query) . first( ) print ( hero) session. commit( )
查找所有数据
from tkinter. font import namesimport sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) query = sqlmodel. select( Hero) heros = session. exec ( query) print ( list ( heros) ) session. commit( )
and 查询
from tkinter. font import namesimport sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) query = sqlmodel. select( Hero) . where( Hero. real_name== "张三" , Hero. name== "张三3" ) hero = session. exec ( query) . first( ) print ( hero) session. commit( )
or 查询
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) query = sqlmodel. select( Hero) . where( sqlmodel. or_( Hero. name == "张三1" , Hero. name == "张三2" , ) ) heros = session. exec ( query) print ( list ( heros) ) session. commit( )
根据 ID 查询
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) hero = session. get( Hero, 1 ) print ( hero) session. commit( )
分页查询
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) query = sqlmodel. select( Hero) . offset( 0 ) . limit( 2 ) heros = session. exec ( query) print ( list ( heros) ) session. commit( )
修改数据
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) hero = session. get( Hero, 1 ) hero. name = "李四" session. commit( ) with sqlmodel. Session( engine) as session: hero = session. get( Hero, 1 ) hero. name = "李四" session. commit( ) session. refresh( hero)
删除数据
import sqlmodel
import typingclass Hero ( sqlmodel. SQLModel, table= True ) : id : typing. Optional[ int ] = sqlmodel. Field( default= None , primary_key= True ) name: str real_name: str age: typing. Optional[ int ] = None db_url = "mysql+pymysql://root:zhangdapeng520@127.0.0.1:3306/fastzdp_sqlmodel?charset=utf8mb4"
engine = sqlmodel. create_engine( db_url, echo= True ) sqlmodel. SQLModel. metadata. drop_all( engine)
sqlmodel. SQLModel. metadata. create_all( engine)
with sqlmodel. Session( engine) as session: zs1 = Hero( name= "张三1" , real_name= "张三" , age= 33 ) session. add( zs1) zs2 = Hero( name= "张三2" , real_name= "张三" , age= 33 ) session. add( zs2) zs3 = Hero( name= "张三3" , real_name= "张三" , age= 33 ) session. add( zs3) hero = session. get( Hero, 1 ) hero. name = "李四" session. commit( ) with sqlmodel. Session( engine) as session: hero = session. get( Hero, 1 ) session. delete( hero) session. commit( )