psycopg2的时间戳与时区和从一个数据库插入选定的数据到另一个数据库的问题

0 人关注

第一个问题，我使用Python中的psycopg2库连接到一个数据库并执行这个查询。

import psycopg2
conn = psycopg2.connect("dbname=**** user=**** password=**** host=**** port=****")
cur = conn.cursor()
cur.execute("SELECT datname, age(datfrozenxid), current_setting('autovacuum_freeze_max_age'), CURRENT_TIMESTAMP FROM pg_database ORDER BY 2 DESC;")
f = cur.fetchone()
print(f)
It returns result like this.
('db_name', 186336925, '200000000', datetime.datetime(2021, 7, 6, 15, 12, 46, 233934, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=420, name=None)))
我希望它能像我在PostgreSQL中查询一样，返回带有时区的时间戳结果。我怎样才能做到呢？
第二个问题，我想把上面的查询结果插入我定义的'f'变量到我的localhost数据库。
    conn1 = psycopg2.connect("dbname=**** user=**** password=**** host=localhost port=5432")
with conn1:
    cur1 = conn1.cursor()
    cur1.execute("DROP TABLE IF EXISTS monitor")
    cur1.execute("CREATE TABLE table(datname NAME, age XID, current_setting TEXT, datetime TIMESTAMP)")
    query = "INSERT INTO table (datname, age, current_setting, datetime) VALUES (%s, %s, %s, %s)"
    cur1.executemany(query, f)
    conn1.commit()
But it returns
TypeError: not all arguments converted during string formatting
我怎样才能修复它？谢谢你的帮助。


           
            
             
              
               wildplasser
              
              ：


           
            
             
              
               
                INSERT INTO table (...
               
               
                -->>
               
               
                INSERT INTO monitor (...


         
          
           
            timestamp-with-timezone


        
         
          
           
            
            
             kittituch bunnag
            
           
           
            发布于
            
            2021-07-06


          
           
            
             
              
              
               Sergii Sholovii
              
             
             
              发布于
              
              2021-07-06


          
           
            
             
              你可以尝试在查询中改变时间戳。
             
             cur.execute("SELECT datname, age(datfrozenxid), current_setting('autovacuum_freeze_max_age'), to_char(CURRENT_TIMESTAMP ,'MM-DD-YYYY HH24:MI:SS')  as t FROM pg_database ORDER BY 2 DESC;")
或者你可以尝试使用 "datetime "模块，检查输出是否是datetime，然后对这个值应用格式。
import psycopg2
import datetime
conn = psycopg2.connect("dbname=****user=*****password=***** host=****port=****")
cur = conn.cursor()
cur.execute("SELECT datname, age(datfrozenxid), current_setting('autovacuum_freeze_max_age'), CURRENT_TIMESTAMP  FROM pg_database ORDER BY 2 DESC;")
f = cur.fetchone()
for row in f:
    if isinstance(row, datetime.datetime):