我正在制作一个nft网站的副本用于练习,当我使用python将数据从一个表传输到我的postgresql数据库中的另一个表时,我遇到了一个错误。发送器表:collection_stats接收表:表一般资产
`
import psycopg2
db_config = {
"host": "SECRET",
"port": SECRET,
"database": "SECRET",
"user": "SECRET",
"password": "SECRET"
}
try:
connection = psycopg2.connect(**db_config)
cursor = connection.cursor()
print("veritabanına bağlantı tm")
except (Exception, psycopg2.Error) as error:
print("veritabanına bağlanırken hata", error)
select_query = "SELECT * FROM collection_stats"
cursor.execute(select_query)
collection_stats_data = cursor.fetchall()
for row in collection_stats_data:
insert_query = """
INSERT INTO tbl_general_assets (
collection_id, address, network, source,
owner_count, unique_owner_count, nft_count, listing_nft_rate,
one_day_change_volume, one_day_change_rate, seven_day_change_volume, seven_day_change_rate,
thirty_day_change_volume, thirty_day_change_rate, volume_top_bid, floor_price
) VALUES (
%f, %f, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
)
"""
cursor.execute(insert_query, (
row['collection_id'], row['address'], row['network'], row['source'],
row['owner_count'], row['unique_owner_rate'], row['nft_count'], row['listing_nft_rate'],
row['one_day_change_volume'], row['one_day_change_rate'], row['seven_day_change_volume'], row['seven_day_change_rate'],
row['thirty_day_change_volume'], row['thirty_day_change_rate'], row['volume_top_bid'], row['floor_price']
))
connection.commit()
connection.close()
`
OUTPUT:veritabanına bağlantı tm Traceback(most recent call last):File“C:\Users\batuh\PycharmProjects\pythonProject\etl.py”,line 35,in row['collection_id'],row['address'],row['network'],row['source'],TypeError:元组索引必须是整数或切片,而不是字符串
1条答案
按热度按时间hiz5n14c1#
默认情况下,psycopg2以Python元组的形式返回行(只能用整数索引)。如果希望按名称访问列,则必须使用另一个游标类,它将行作为dict或类似dict的对象返回。这在文档的“psycopg2.extras”部分的“Connection and Cursor Subclasses”小节中进行了描述。
话虽如此,你 * 确实 * 意识到这可以通过
INSERT INTO tbl_general_assets (foo, bar, fie) SELECT foo, bar, fie FROM collection_stats
更有效地完成,不是吗?