英文:
How to use for loop to run sql query in python
问题
import pandas as pdA = ['A001', 'B001', 'C001', 'Bdd', 'djd.djsx']AB = pd.DataFrame(A, columns=['app_name'])# Your SQL querysql_template = '''select COUNT(DISTINCT [R_ID])from database_viewwhere [app_name] = '{}''''# Create an empty DataFrame to store the final resultsfinal_result = pd.DataFrame(columns=['app_name', 'count'])# Loop through each app_name in ABfor 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_resultfinal_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'])ABapp_name0 A0011 B0012 C0013 Bdd4 djd.djsx
I have SQL query looks like this
select COUNT (DISTINCT [R_ID])from database_viewwhere [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_viewwhere [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_countFROM database_viewGROUP 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_countFROM database_viewGROUP 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_viewwhere [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_viewwhere [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_viewwhere [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_viewwhere [app_name] in {i}GROUP BY app_name'''a=pd.read_sql_query(sql,database)final = df.append(a,ignore_index = True)print(final)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论