The read_sql
函数允许您将数据从 SQL 数据库直接加载到Pandas数据框。
它允许您直接解析和执行 SQL 查询或将整个表读入数据框。通过使用pandas.read_sql
,您正在 SQL 数据库和 Pandas 之间建立无缝桥梁。
Pandas read_sql 函数的语法
的基本语法read_sql
Pandas 中的函数如下:
pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)
以下是每个参数的含义:
-
sql
:该参数可以是要执行的字符串 SQL 查询或 SQLalchemy Selectable(表示已编译的 SQL 语句)。
-
con
:这是您的数据库连接对象,可以是 SQLAlchemy 引擎或 sqlite3 连接。
-
index_col
:可选参数,指定要设置为索引的列。
-
coerce_float
:当设置为 True(默认)时,它会尝试将非字符串数据转换为浮点数。
-
params
:要传递到的参数的可选列表或字典sql
query.
-
parse_dates
:一个可选参数,用于将列解析为日期时间。
-
columns
:如果您正在读取表(而不是查询),这允许您选择要加载的列。
-
chunksize
:如果指定的话,read_sql
将返回一个迭代器,其中 chunksize 是每个块的行数。
建立连接
在熊猫中read_sql
函数中,您可以通过多种方式建立与 SQL 数据库的连接。然而,最常见的两种方式是:
使用sqlite3连接
如果您正在与SQLite数据库,也可以直接使用sqlite3连接。这是一个例子:
import sqlite3
import pandas as pd
con = sqlite3.connect('school_db.sqlite')
使用 SQLAlchemy 引擎
您可以创建一个 SQLAlchemy 引擎并将其用作连接参数read_sql
功能。此方法的优点是能够使用许多不同类型的 SQL 数据库。
首先,您需要安装 SQLAlchemy 引擎:
pip install sqlalchemy
下面是连接到 SQLite 数据库的示例:
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('sqlite:///school_db.sqlite')
MySQL
要连接到 MySQL 数据库,您需要安装 mysql-connector-python 包,它是 Python 的 MySQL 连接器,SQLAlchemy 将使用它与 MySQL 服务器进行通信:
pip install mysql-connector-python
安装连接器后,您可以为 MySQL 数据库创建引擎:
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://USERNAME:PASSWORD@HOST/DB_NAME')
PostgreSQL
对于 PostgreSQL,您需要使用 psycopg2-binary 包,它是最流行的 Python PostgreSQL 适配器。
pip install psycopg2-binary
安装适配器后,您可以为 PostgreSQL 数据库创建引擎:
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://USERNAME:PASSWORD@HOST/DB_NAME')
Oracle
要使用 Python 与 Oracle 数据库交互,我们可以利用 cx_Oracle 库。
首先,您需要使用 pip 安装 cx_Oracle 包:
pip install cx_Oracle
安装cx_Oracle后,您可以建立与Oracle数据库的连接:
from sqlalchemy import create_engine
engine = create_engine('oracle+cx_oracle://USERNAME:PASSWORD@HOST:PORT/DB_NAME')
基本SQL数据库Queries
首先,您需要导入必要的库并建立与数据库的连接:
import pandas as pd
import sqlite3
con = sqlite3.connect("school_database.db")
现在,我们假设数据库中有一个名为“users”的表。我们可以用read_sql
将整个“users”表加载到 DataFrame 中:
df = pd.read_sql("SELECT * FROM users", con)
con.close()
print(df.head())
Output:
id name age
0 1 Alice 20
1 2 Bob 22
2 3 Charlie 19
3 4 David 21
4 5 Emma 20
在上面的代码中,我们执行了 SQL 查询“SELECT * FROM users”,该查询从“users”表中选择所有列和行。
The read_sql
函数执行此查询并将结果加载到 Pandas 数据框中,df
.
在通过调用读取数据后,我们不会忘记关闭连接con.close()
以避免资源消耗。
The df.head()
然后函数打印数据帧的前 5 行。
读取表数据
The read_sql
Pandas 中的函数允许我们使用上面看到的 SQL 查询字符串或表名,将数据从 SQL 数据库获取到 DataFrame 对象中。
当我们向read_sql
函数,它将读取整个表并将其转换为 DataFrame。
让我们通过一个例子来看看如何做到这一点。假设我们的数据库中有一个名为“students”的表。
df = pd.read_sql('students', con)
con.close()
print(df.head())
Output:
id name age grade
0 1 Alice 20 10
1 2 Bob 22 11
2 3 Charlie 19 10
3 4 David 21 12
4 5 Emma 20 11
在此示例中,我们使用read_sql
使用“students”表作为 SQL 查询的函数。该函数从“students”表中获取所有行并将它们转换为 DataFrame。
过滤行
Pandas’ read_sql
允许您运行 SQL 查询,这意味着您可以添加WHERE
根据特定条件过滤行的子句。
让我们继续使用“users”表并将其过滤为仅包含年龄超过 20 岁的用户:
df = pd.read_sql("SELECT * FROM users WHERE age > 20", con)
con.close()
print(df)
Output:
id name age
0 2 Bob 22
1 4 David 21
在此代码中,我们执行 SQL 查询“SELECT * FROM users WHEREage > 20”。此查询从“users”表中选择所有列,但仅选择“age”大于 20 的行。
The read_sql
然后函数执行此查询并将结果加载到数据框中df
.
我们还可以添加多个条件。
例如,让我们过滤“users”表以仅包含年龄超过 20 岁且姓名以“D”开头的用户:
df = pd.read_sql("SELECT * FROM users WHERE age > 20 AND name LIKE 'D%'", con)
con.close()
print(df)
Output:
id name age
0 4 David 21
在此代码中,SQL 查询“SELECT * FROM users WHEREage > 20 AND name LIKE 'D%'”从 'users' 表中选择所有列,但仅选择 'age' 大于 20 且 'name' 的行' 以 'D' 开头。
The read_sql
函数执行此查询并将结果加载到数据框中df
.
提取特定列
您可以使用columns
中的参数read_sql
如果您不想读取整个表而只想读取特定列,请使用此函数。
这是一种仅加载您需要的数据的简单而有效的方法。
让我们继续我们的“users”表并仅提取“name”和“age”列:
df = pd.read_sql('users', engine, columns=['name', 'age'])
con.close()
print(df.head())
Output:
name age
0 Alice 20
1 Bob 22
2 Charlie 19
3 David 21
4 Emma 20
在此代码中,read_sql
函数仅读取“users”表中的“name”和“age”列。其余代码的工作方式与以前相同。
设置DataFrame索引
The index_col
的参数read_sql
函数指定哪一列应用作 DataFrame 的索引。
让我们考虑一个示例,其中有一个“students”表,其中有一个“id”列,我们希望将其用作索引:
df = pd.read_sql("SELECT * FROM students", con, index_col='id')
con.close()
print(df.head())
Output:
name age grade
id
1 Alice 20 10
2 Bob 22 11
3 Charlie 19 10
4 David 21 12
5 Emma 20 11
在此代码中,我们使用pd.read_sql
执行 SQL 查询“SELECT * FROM Students”并将结果加载到 pandas 数据框中。
我们还设置了index_col='id'
使用学生表中的“id”列作为 DataFrame 的索引。
参数化查询
在防止 SQL 注入攻击和创建动态查询方面,参数化查询是一个至关重要的功能。
The read_sql
pandas 中的函数通过以下方式支持参数化查询params
争论。
让我们考虑一个场景,我们需要根据用户年龄过滤“users”表,但具体年龄将在运行时决定。我们可以通过以下方式实现这一目标:
age_to_filter = 20
df = pd.read_sql("SELECT * FROM users WHERE age > ?", con, params=[age_to_filter])
con.close()
print(df)
Output:
id name age
0 2 Bob 22
1 4 David 21
在此代码中,我们执行 SQL 查询“SELECT FROM users WHEREage > ?”,其中“?”是一个占位符,将被替换params
争论。
我们通过age_to_filter
变量作为params
争论。
因此,执行的 SQL 查询是“SELECT FROM users WHEREage > 20”,它选择“users”表中“age”大于 20 的所有列。
The read_sql
函数执行此查询并将结果加载到数据框中df
.
我们还可以使用字典或者元组来传递参数:
df = pd.read_sql("SELECT * FROM users WHERE age > :age_to_filter", con, params={"age_to_filter": 20})
con.close()
print(df)
Output:
id name age
0 2 Bob 22
1 4 David 21
在这种情况下,SQL 查询使用命名占位符“:age_to_filter”,该占位符被替换为params
字典。
解析日期列
您可以使用parse_dates
的参数read_sql
函数来解析日期列,这是因为 Pandas 默认将日期列加载为对象。
The parse_dates
参数接受要解析为日期的列列表。
让我们考虑一个例子,其中有一个带有“dob”(出生日期)列的“students”表:
df = pd.read_sql("SELECT * FROM students", con, parse_dates=['dob'])
con.close()
print(df.head())
print("\nData type of 'dob' column:", df['dob'].dtypes)
Output:
id name dob grade
0 1 Alice 2003-05-15 10
1 2 Bob 2001-06-20 11
2 3 Charlie 2004-09-10 10
3 4 David 2002-11-25 12
4 5 Emma 2003-08-05 11
Data type of 'dob' column: datetime64[ns]
一旦“dob”列采用日期时间格式,您就可以对此列执行各种日期时间操作。
例如,您可以提取出生年份、计算年龄、选择特定日期之间的行等等。
使用块处理大型数据集
当处理无法放入内存的大型数据集时,可以使用chunksize
参数输入read_sql
.
这将返回类型的可迭代对象TextFileReader
,允许您分块处理数据。
如果我们有一个很大的“users”表,并且我们希望一次处理 500 行的数据块:
from sqlalchemy import create_engine
engine = create_engine('mysql+mysqlconnector://USERNAME:PASSWORD@HOST/DB_NAME')
#Enable streaming to load only records when Pandas fetch them
con = engine.connect().execution_options( stream_results=True)
chunks = pd.read_sql("SELECT * FROM users", con, chunksize=500)
for chunk in chunks:
print(chunk)
请注意,我们通过设置启用了流媒体stream_results=True
当 Pandas 获取记录时仅加载记录,而不是加载所有记录。
您可以根据需要在循环中处理每个块。
假设我们要计算每个块的平均年龄。我们可以修改循环来做到这一点:
chunks = pd.read_sql("SELECT * FROM users", con, chunksize=500)
for i, chunk in enumerate(chunks, start=1):
avg_age = chunk['age'].mean()
print(f"Average age for chunk {i}: {avg_age}")
这将打印每组 500 行的平均年龄。
Using chunksize
是一个功能强大的工具,可让您处理大于机器内存的数据集。
执行存储过程
存储过程是 SQL 数据库的一个强大功能。它们是执行特定任务并存储在数据库本身中的 SQL 语句集。
在 Python 中调用存储过程read_sql
,您可以将调用编写为常规 SQL 语句。这是一个例子:
df = pd.read_sql("CALL get_students_by_age(20)", con)
con.close()
print(df.head())
在此代码中,我们使用 SQLCALL
执行存储过程的语句get_students_by_age
,传递 20 作为参数。
存储过程的结果被加载到 Pandas DataFrame 中df
.
Note:调用存储过程的具体语法可能会有所不同,具体取决于您使用的 SQL 数据库。
调用存储过程是执行复杂数据库操作的一种非常有效的方法,因为这些操作是直接在数据库服务器上执行的。