python 我想在postgresql中将数据从一个表转移到另一个表

xyhw6mcr  于 2023-09-29  发布在  Python
关注(0)|答案(1)|浏览(110)

我正在制作一个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:元组索引必须是整数或切片,而不是字符串

hiz5n14c

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更有效地完成,不是吗?

相关问题