将 while 循环与 SQL 查询和 Pandas 一起使用

发布于 2025-01-20 18:39:47 字数 861 浏览 4 评论 0原文

我正在 python 中运行 SQL 查询并将表的结果存储在 Pandas DataFrame 中。我想运行查询直到满足条件。以下是一些可供使用的示例数据：

sql table1

name      val       
post      10
sutter    15
oak       20

import pandas as pd
# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user1'
pwd = 'pwd'
host =  'xxxx.1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))


# Readdata
con = engine.connect()

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''.format(v)

df = pd.read_sql(query, con)

我想运行此查询，直到满足条件。条件是数据帧的大小或返回的行数。上述查询返回 1 行。我需要查询来更新 v 的值，直到满足条件。因此，while-loop 就可以了。

While df.shape[0] => 2:

   run query

如何在 while-loop 中运行查询并检查返回的行数或数据帧的大小？

原文

I am running a SQL query in python and storing the results of the table in a Pandas DataFrame. I'd like run the query until a condition in met. Here's some sample data to work with:

sql table1

name      val       
post      10
sutter    15
oak       20

import pandas as pd
# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user1'
pwd = 'pwd'
host =  'xxxx.1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))


# Readdata
con = engine.connect()

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''.format(v)

df = pd.read_sql(query, con)

I'd like to run this query until a condition is met. Condition is the size of the dataframe or number of rows returned. Above query returns 1 row. I need the query to update the value of v until the condition is satisfied. So, a while-loop would work.

While df.shape[0] => 2:

   run query

How do I run the query in a while-loop and check of # of rows returned or size of the dataframe?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你曾走过我的故事 2025-01-27 18:39:47

如果您确实需要使用循环来执行此操作，则可以使用 while True:，接下来获取数据，接下来使用 if 检查行数并使用 break 退出循环，并使用 v += 1 运行具有更大值的循环

类似

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''

while True:

    df = pd.read_sql(query.format(v), con)

    if df.shape[0] >= 2:
        break

    v += 1

但如果数据库中只有一行，那么它可能会永远工作，并且可能需要其他条件来停止它。 IE。 当 v < 10000：

If you really need to do it with loop then maybe use while True:, next get data, next use if to check number of rows and use break to exit loop, and use v += 1 to run loop with bigger value

Something like

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''

while True:

    df = pd.read_sql(query.format(v), con)

    if df.shape[0] >= 2:
        break

    v += 1

but if there is only one row in database then it may work forever and it may need other condition to stop it. ie. while v < 10000:

回复收藏 0 原文

~没有更多了~