Question 253717 in PostgreSQL Question posted in
The official documentation can be found here.

I want to transfer data from one table to another in my postgresql database using python

I’m making a copy of an nft website for practice and I’m getting an error when transferring data from one table to another in my postgresql database using python. sender table: collection_stats
receiving table:
tbl_general_assets

`
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:UsersbatuhPycharmProjectspythonProjectetl.py", line 35, in
row[‘collection_id’], row[‘address’], row[‘network’], row[‘source’],
TypeError: tuple indices must be integers or slices, not str

Tags:

2

Answers


  1. 0 
    ssh production
pg_dump -C -Fp -f dump.sql -U postgres psycopg2
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql
    Login or Signup to reply.
  2. 0

    By default, psycopg2 returns rows as Python tuples (which can only be indexed by integers). If you want to access the columns by name, you have to use an alternative cursor class that returns rows as, for example, dicts or dict-like objects. This is described in the "Connection and Cursor Subclasses" subsection of the "psycopg2.extras" section of the documentation.

    Having said that – you do realize that this can be done much more efficiently by INSERT INTO tbl_general_assets (foo, bar, fie) SELECT foo, bar, fie FROM collection_stats, don’t you?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search