时间戳

首页 > 解决方案 > Python, Pandas, sql, read specific columns from a database with a for-loop

python - Python, Pandas, sql, read specific columns from a database with a for-loop

问题描述

my goal is to read only certain column names with pd.read_sql

## FILTER VALUES ##
#filtering all columns with temperature
filter_temp_values = df_column[df_column[0].str.contains("temperature")]
#filtering all columns with humidity
filter_humi_values = df_column[df_column[0].str.contains("humidity")]

result = cursor.fetchall()
for x in filter_temp_values[0]:
    #y = tuple(list(x))
    y = "SELECT '%s' FROM Raummonitoring" %(x)
    test = cursor.execute(y)

result:

InternalError: Unread result found

i tried also:

result = cursor.fetchall()
for x in filter_temp_values[0]:
    y = tuple(list(x))
    z = f"SELECT ${y} FROM Raummonitoring"
    test = pd.read_sql(z, con = engine)

result:

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1370 (42000): execute command denied to user 'xxxx'@'%' for routine 'xxxx.$' [SQL: SELECT $('t', 'e', 'm', 'p', 'e', 'r', 'a', 't', 'u', 'r', 'e', '', 'e', 'r', 's', '', 'l', 'i', 't', 'e', '', '3', '', 'w', 'e', 'r', 'm', 's', 'e', 'r', '', '0', '', 'e', 'l', 's', 'y', 's', '_', '0') FROM Raummonitoring] (Background on this error at: http://sqlalche.me/e/14/f405)

what is my fault ?

标签： pythonmysqlpandasdatabasedatabase-cursor

解决方案

似乎您可能会遇到将字符串列名转换为其字符元组的问题。

像这样的工作，您首先使用“，”加入连接列表，然后只向您的数据库提交 1 次？

column_names = ['temperature_1', 'temperature_2', 'temperature_3']
y = ', '.join(column_names)
z = f"SELECT {y} FROM Raummonitoring"
    
print(z)

从 Raummonitoring 中选择温度_1、温度_2、温度_3

推荐阅读