SqlAlchemy操作（二）

 

SQLALchemy初始化链接数据库

1. 数据库配置.

https://www.cnblogs.com/mengbin0546/p/10124560.html

 

2.  python端操作.

一、  代码：

from django.shortcuts import render

# Create your views here.

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

#创建数据库连接，max_overflow 指定最大连接数.
engine =create_engine("mysql+pymysql://root:123456@132.232.55.209:3306/db1",)

DBSession =sessionmaker(engine)  #创建DBSession类型

session =DBSession()#创建session对象.

BaseModel=declarative_base()#创建对象的基类.

class User(BaseModel): #定义User对象
        __tablename__ ="user1" #创建表，指定表名称.
        #指定表结构
        id = Column(String(10),primary_key=True)
        username =Column(String(10),index=True)

# class Session(BaseModel):
#         __tablename__ ="session1"
#         id =Column(String(10),primary_key=True)
#         user =Column(String(10),index=True)
#         ip =Column(String(10))

# 创建表，执行所有BaseModel类的子类create_all,进行创建表
# BaseModel.metadata.create_all(engine)

# 删除表，执行drop_all 方法进行删除表.
BaseModel.metadata.drop_all(engine)

session.commit() #提交

 

执行后在数据库中查看：

MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| mengbin       |
| session       |
| users         |
+---------------+
3 rows in set (0.00 sec)

 

 

二、数据库增删改查.

 

#1. 增加
# (方法一)
# user_obj =User(id =1,username ="wupeiqi")
# session.add(user_obj)
# session.commit()

#(方法二)
# session.add_all([User(id =3,username="san"),
#                  User(id =4,username="si"),
#                 ])
# session.commit()


#2.删除.
# session.query(User).filter(User.id >=3).delete()
# session.commit()

#3.修改
session.query(User).filter(User.id ==1).update({"username" :"helloworld"})
session.commit()

#4.查询.
obj = session.query(User).filter(User.id==1)
print("obj",obj)

obj1= session.query(User).filter(User.id>=1).all()
print("obj1",obj1)

for i in obj1:
        print(i.username)


4.查询结果：

　　obj SELECT user1.id AS user1_id, user1.username AS user1_username FROM user1 WHERE user1.id = %(id_1)s
　　

　　　obj1 [<__main__.User object at 0x00000065D55BC278>, <__main__.User object at 0x00000065D55BC2E8>, <__main__.User object at 0x00000065D55BC358>]

 

 

三、ORM操作数据库,通过delect执行sql语句.

from sqlalchemy import create_engine
#创建引擎
engine = create_engine("mysql+pymysql://root:dadmin01@127.0.0.1:3306/meng", max_overflow=5)
print(engine)  # 打印结果： Engine(mysql+pymysql://root:***@127.0.0.1:3306/meng)
 
#操作添加数据sql语句方法1
engine.execute("INSERT INTO bin (name) VALUES ('33')")

#操作添加数据sql语句方法2
engine.execute("INSERT INTO bin (name) VALUES ( %s)",(( "v1",),( "v1",),))

#操作查询数据sql语句(有返回值)
result = engine.execute("select * from bin")
print(result) #(<sqlalchemy.engine.result.ResultProxy object at 0x00000097CF9B6E48>
res =result.fetchall()  #查询所有结果;
print(res)  #[('dd',), ('dd',), ('dd',), ('dd',), ('sdfs',), ('sdfs',), ('sdfs',), ('sdfs',),]

 

 

 

四、使用__repr__定义返回的数据

 

class TableTest(BaseModel):
        __tablename__ ="tabletest"
        nid = Column(Integer,primary_key=True,autoincrement=True)
        name =Column(String(10),nullable=False)
        role =Column(Integer)

        def __repr__(self):
                output ="(%s，%s,)"%(self.nid,self.name)
                return output


#创建表，执行所有BaseModel类的子类,进行创建表
BaseModel.metadata.create_all(engine)

res =session.query(TableTest).all()

print("res",res)