英文:
How to use for loop to run sql query in python
问题
import pandas as pd
A = ['A001', 'B001', 'C001', 'Bdd', 'djd.djsx']
AB = pd.DataFrame(A, columns=['app_name'])
# Your SQL query
sql_template = '''
select COUNT(DISTINCT [R_ID])
from database_view
where [app_name] = '{}'
'''
# Create an empty DataFrame to store the final results
final_result = pd.DataFrame(columns=['app_name', 'count'])
# Loop through each app_name in AB
for i in range(len(AB)):
app_name = AB.loc[i, 'app_name']
sql = sql_template.format(app_name)
result = pd.read_sql_query(sql, database)
# Append the result to final_result
final_result = final_result.append({'app_name': app_name, 'count': result.iloc[0, 0]}, ignore_index=True)
final_result
This code should execute your SQL query for each app_name in the AB DataFrame, and append the results to the final_result DataFrame in the format you expect.
英文:
I have 100 records in table A， I only list 4 here but please use loop to solve this question
A = ['A001', 'B001','C001','Bdd','djd.djsx']
AB = pd.DataFrame(A, columns = ['app_name'])
AB
app_name
0 A001
1 B001
2 C001
3 Bdd
4 djd.djsx
I have SQL query looks like this
select COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in ('A001')
I want to use loop to execute the query for 100 times and append the final result to the AB
What I tried is:
For i in AB:
sql = '''select COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in {i}'''
a=pd.read_sql_query(sql,database)
final = AB.append(a,ignore_index = True)
final
but it does not work, and also I find it will add one more column to the right of df"AB", but it will append the count number to a new row at last.
Expected Result looks likes this,
expected result
it means for app A001, it has 6 different R_ID,
for app B001 it has 1 different R_ID
Any help is really appreicated!
答案1
得分: 1
考虑一个单一的聚合查询，使用GROUP BY，无需任何for循环，并且无需追加到数据框中。一旦SQL查询运行，使用DataFrame.query来筛选结果到你的100个应用程序列表中。
sql = '''SELECT [app_name], COUNT(DISTINCT [R_ID]) AS r_id_count
FROM database_view
GROUP BY [app_name]
'''
apps = AB["app_name"].tolist()
app_counts_df = (
pd.read_sql_query(sql, database)
.query("app_name == @apps")
)
英文:
Consider a single aggregate query using GROUP BY without any for-loop and the need to append to data frame. Once SQL query runs, filter result with DataFrame.query to your list of 100 apps.
sql = '''SELECT [app_name], COUNT(DISTINCT [R_ID]) AS r_id_count
FROM database_view
GROUP BY [app_name]
'''
apps = AB["app_name"].tolist()
app_counts_df = (
pd.read_sql_query(sql, database)
.query("app_name == @apps")
)
答案2
得分: 0
你忘记在 for 循环和 SQL 查询的 f-string 中加入列名。尝试使用以下代码：
for i in AB['app_name']:
sql = f'''select COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in {i}'''
更新-1：编辑 SQL 查询：
# 创建一个空的数据框：
df = pd.DataFrame()
for i in AB['app_name']:
sql = f'''select app_name, COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in {i}
GROUP BY app_name'''
a = pd.read_sql_query(sql, database)
final = df.append(a, ignore_index=True)
print(final)
英文:
You forgot to put column name in for loop and f-string in SQL query. Try this code:
for i in AB['app_name']:
sql = f'''select COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in {i}'''
Update-1: Edit SQL query:
# Create an empty dataframe:
df=pd.DataFrame()
for i in AB['app_name']:
sql = f'''select app_name,COUNT (DISTINCT [R_ID])
from database_view
where [app_name] in {i}
GROUP BY app_name'''
a=pd.read_sql_query(sql,database)
final = df.append(a,ignore_index = True)
print(final)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论