qcBlog Python查询mysql数据的各种方式

Python查询mysql数据的各种方式

发布时间：2021-02-01 23:44:32

修改时间：2023-09-09 08:36:58

总阅读数：872

今日阅读数：0

昨日日阅读数：0

字数：4366

一、flask_sqlalchemy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app=Flask(__name__)
# 数据库的uri=mysql://用户名:密码@服务器地址/数据库名称
app.config["SQLALCHEMY_DATABASE_URI"]="mysql://root:123456@127.0.0.1:3306/blog"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"]=False
db = SQLAlchemy(app)

# orm类
class Blog(db.Model):
# 表名
__tablename__="blog"
id = db.Column(db.Integer,primary_key=True)
title = db.Column(db.String)
content = db.Column(db.String)

def __repr__(self):
return "title:"+self.title

if __name__ == '__main__':
# 查询全部
print(Blog.query.all())
# 过滤:filter、排序:order_by、分组:group_by
print(Blog.query.filter(Blog.id<20).order_by(Blog.id)[1:3])

二、Sqlalchemy
1．使用orm的方式

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


Base = declarative_base()

# orm类
class Blog(Base):
# 表名
__tablename__="blog"
id = Column(Integer,primary_key=True)
title = Column(String)
content = Column(String)

def __repr__(self):
return "title:"+self.title


if __name__ == '__main__':
# 使用create_engine创建engine对象
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/blog")
# 使用sessionmaker创建一个Session
Session = sessionmaker(engine)
# 声明一个session变量为Session()
session = Session()
# 通过session进行查询
print(session.query(Blog).all())

2．使用SQL语句的方式

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:123456@127.0.0.1/blog")
Session = sessionmaker(engine)
session=Session()
# 调用session的execute方法传入SQL语句
resultProxy = session.execute("select * from blog")
# 调用fetchall放回执行结果
print(resultProxy.fetchall())

三、Peewee
1．使用Orm的方式

import peewee
from peewee import MySQLDatabase

# 实例化一个MySQLDatabase
db = MySQLDatabase('blog', user='root', passwd='123456')
# 定义一个orm类，继承peewee.Model
class Blog(peewee.Model):
id=peewee.IntegerField()
title=peewee.TextField()
content=peewee.TextField()

class Meta:
database = db


if __name__ == '__main__':
# 调用orm类的select()方法获取查询结果
print(list(Blog.select()))

2．SQL语句的方式

from peewee import MySQLDatabase

db = MySQLDatabase(host="127.0.0.1",
user="root",
passwd="123456",
database="mall_goods")
# 调用db的cursor()返回一个cursor对象
cursor = db.cursor()
# 调用cursor对象的execute传入SQL语句
cursor.execute("select * from goods_spu")
# 调用cursor的fetchall方法得到执行结果
res = cursor.fetchall()
print(res)

四、Pymysql

import pymysql

if __name__ == '__main__':
# 调用pymysql下的connect方法实例化一个db
db = pymysql.connect("127.0.0.1","root","123456","blog")
# 调用db的cursor方法得到一个cursor
cursor = db.cursor()
# 调用cursor的execute方法执行SQL语句
cursor.execute("select * from blog")
# 调用cursor的fetchall方法得到执行结果
res = cursor.fetchall()
for row in res:
print(row)

五、Mysql_connector(mysql官方提供的方式)

import mysql.connector

# 调用mysql.connector.connect得到db
db = mysql.connector.connect(host="127.0.0.1",
user="root",
passwd="123456",
db="blog")
# 调用db的cursor方法得到cursor
cursor = db.cursor()
# 调用cursor的execute执行SQL语句
cursor.execute("select * from blog")
# 调用cursor的fetchall得到执行结果
print(cursor.fetchall())

六、SqlObject

import sqlobject
from sqlobject.mysql import builder

# 调用sqlobject.mysql下的builder()()得到conn
conn = builder()(host="127.0.0.1",
user="root",
password="123456",
db="blog",
charset="utf8")

# 定义orm类
class Blog(sqlobject.SQLObject):
_connection = conn
# id = sqlobject.IntCol()
title = sqlobject.StringCol()
content = sqlobject.StringCol()
# def __repr__(self):
# return self.title


# 调用orm类的select()得到查询结果
res = list(Blog.select())
print(res)

七、MySQLdb

import MySQLdb

db = MySQLdb.connect(host="127.0.0.1",
user="root",
passwd="123456",
charset="utf8")
cursor = db.cursor()
print(cursor.execute("use blog"))
print(cursor.execute("select * from blog"))
print(cursor.fetchall()