pymysql 连接 mysql 数据库
此方式连接mysql服务器,方法简明直接,但是操作数据库,需要使用mysql数据的语法较为不便。
import pymysql
db = pymysql. connect( host= 'localhost' , user= 'root' , database= 'testbase' , password= '******' , charset= 'utf8' )
try :
with db. cursor( ) as cursor:
sql = """SELECT VERSION()"""
cursor. execute( sql)
res = cursor. fetchone( )
db. commit( )
except Exception as e:
print ( f'fetch error{e}' )
finally :
db. close( )
print ( f'Database version : {res}' )
sqlalchemy 连接 mysql 数据库
sqlalchemy的core方式比ORM方式更为底层。
import pymysql
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
engine = create_engine( "mysql+pymysql://root:xsh123456@localhost/testbase" , echo= True )
metadata = MetaData( engine)
book_table = Table( 'book' , metadata,
Column( 'id' , Integer, primary_key= True ) ,
Column( 'name' , String( 20 ) ) )
author_table = Table( 'author' , metadata,
Column( 'id' , Integer, primary_key= True ) ,
Column( 'book_id' , None , ForeignKey( 'book.id' ) ) ,
Column( 'author_name' , String( 128 ) , nullable= False ) )
try :
metadata. create_all( )
except Exception as e:
print ( f"create error{e}" )
ORM 方式连接 mysql 数据
sqlalchemy的ORM方式,更贴近实际应用,使用频次最高 。
import pymysql
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey, DateTime
from sqlalchemy. ext. declarative import declarative_base
from datetime import datetime
Base = declarative_base( )
class Book_table ( Base) :
__tablename__ = 'bookorm'
book_id = Column( Integer( ) , primary_key= True )
book_name = Column( String( 50 ) , index= True )
class Author_table ( Base) :
__tablename__ = 'authororm'
user_id = Column( Integer( ) , primary_key= True )
username = Column( String( 15 ) , nullable= False , unique= True )
create_on = Column( DateTime( ) , default= datetime. now)
update_on = Column( DateTime( ) , default= datetime. now, onupdate= datetime. now)
dburl = 'mysql+pymysql://root:xsh123456@localhost/testbase?charset=utf8mb4'
engine = create_engine( dburl, echo= True , encoding= 'utf-8' )
Base. metadata. create_all( engine)