有哪几种方式从mysql拿取数据,详细代码
时间: 2024-04-29 12:23:00 浏览: 8
1. 使用SQL语句查询数据
```python
import mysql.connector
# 连接数据库
mydb = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="database_name"
)
# 创建游标对象
mycursor = mydb.cursor()
# 编写SQL查询语句
sql = "SELECT * FROM customers"
# 执行SQL查询语句
mycursor.execute(sql)
# 获取查询结果
result = mycursor.fetchall()
# 遍历结果
for row in result:
print(row)
```
2. 使用ORM框架查询数据
```python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 创建数据库连接
engine = create_engine('mysql+mysqlconnector://username:password@localhost:3306/database_name')
# 创建会话工厂
Session = sessionmaker(bind=engine)
# 创建模型基类
Base = declarative_base()
# 定义模型类
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
name = Column(String(255))
email = Column(String(255))
# 创建会话对象
session = Session()
# 查询数据
customers = session.query(Customer).all()
# 遍历查询结果
for customer in customers:
print(customer.id, customer.name, customer.email)
```
3. 使用pandas库查询数据
```python
import mysql.connector
import pandas as pd
# 连接数据库
mydb = mysql.connector.connect(
host="localhost",
user="username",
password="password",
database="database_name"
)
# 编写SQL查询语句
sql = "SELECT * FROM customers"
# 使用pandas读取查询结果
df = pd.read_sql(sql, con=mydb)
# 打印查询结果
print(df)
```