python - 在网上找不到我的问题的解决方案。我正在从 API 获取 json 数据，我想将它插入到 postgresql

Question

conn = psycopg2.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST)
cur = conn.cursor()
cur.execute("CREATE TABLE sar_exchange_currency3 (id SERIAL PRIMARY KEY, exchange JSONB NOT NULL);")
cur.execute("CREATE INDEX idxcustomerstags ON sar_exchange_currency3 USING gin ((exchange->'tags'));")
cur.execute("INSERT INTO sar_exchange_currency3 (exchange) VALUES(%s)", (exchange_rate,))
conn.commit()
cur.close()
conn.close()

exchange_rate 包含 json 数据，如何将其插入表中？我创建表格的方式是否正确？通过使用 JSONB?? 如果没有，请为我提供正确的方法。

exchange_rate = {'AED': 3.6732, 'AFN': 79.600004, 'ALL': 103.85, 'AMD': 494.838376, 'ANG': 1.7947, 'AOA': 639.873, 'ARS': 96.3639, 'AUD': 1.359251, 'AWG': 1.8005, 'AZN': 1.700805, 'BAM': 1.661284, 'BBD': 2, 'BDT': 84.781277, 'BGN': 1.660026, 'BHD': 0.376956, 'BIF': 1985, 'BMD': 1, 'BND': 1.367683, 'BOB': 6.893995, 'BRL': 5.2012, 'BSD': 1, 'BTC': 3.1293741e-05, 'BTN': 74.56844, 'BWP': 11.08509, 'BYN': 2.530281, 'BZD': 2.015374, 'CAD': 1.25599, 'CDF': 2003, 'CHF': 0.917233, 'CLF': 0.0273, 'CLP': 753.69, 'CNH': 6.463165, 'CNY': 6.468, 'COP': 3826.09719, 'CRC': 619.867868, 'CUC': 1, 'CUP': 25.75, 'CVE': 93.75, 'CZK': 21.7694, 'DJF': 178, 'DKK': 6.303614, 'DOP': 57.1, 'DZD': 134.537443, 'EGP': 15.6696, 'ERN': 15.004625, 'ETB': 44.1, 'EUR': 0.847448, 'FJD': 2.0899, 'FKP': 0.729078, 'GBP': 0.729078, 'GEL': 3.11, 'GGP': 0.729078, 'GHS': 5.955, 'GIP': 0.729078, 'GMD': 51.1, 'GNF': 9830, 'GTQ': 7.746599, 'GYD': 209.182046, 'HKD': 7.77269, 'HNL': 23.935001, 'HRK': 6.3778, 'HTG': 94.016246, 'HUF': 304.623152, 'IDR': 14473.962328, 'ILS': 3.27824, 'IMP': 0.729078, 'INR': 74.4227, 'IQD': 1459.5, 'IRR': 42152.500136, 'ISK': 125.44, 'JEP': 0.729078, 'JMD': 154.490737, 'JOD': 0.709, 'JPY': 110.26766667, 'KES': 108.2, 'KGS': 84.739652, 'KHR': 4078, 'KMF': 417.349846, 'KPW': 900, 'KRW': 1149.958735, 'KWD': 0.300742, 'KYD': 0.83328, 'KZT': 427.492324, 'LAK': 9560, 'LBP': 1527, 'LKR': 199.469418, 'LRD': 171.599983, 'LSL': 14.55, 'LYD': 4.515, 'MAD': 8.9525, 'MDL': 18.026229, 'MGA': 3815, 'MKD': 52.369217, 'MMK': 1645.765181, 'MNT': 2848.112285, 'MOP': 8.006268, 'MRO': 356.999828, 'MRU': 36.12, 'MUR': 42.50072, 'MVR': 15.4, 'MWK': 810, 'MXN': 20.201285, 'MYR': 4.2325, 'MZN': 63.674988, 'NAD': 14.55, 'NGN': 411, 'NIO': 35.3075, 'NOK': 8.872277, 'NPR': 119.310787, 'NZD': 1.434509, 'OMR': 0.385015, 'PAB': 1, 'PEN': 3.952, 'PGK': 3.5, 'PHP': 50.268231, 'PKR': 160.9, 'PLN': 3.878473, 'PYG': 6850.365336, 'QAR': 3.641, 'RON': 4.1735, 'RSD': 99.855598, 'RUB': 74.0669, 'RWF': 990, 'SAR': 3.750613, 'SBD': 8.028936, 'SCR': 16.179819, 'SDG': 446, 'SEK': 8.68306, 'SGD': 1.363395, 'SHP': 0.729078, 'SLL': 10253.950087, 'SOS': 586, 'SRD': 21.307, 'SSP': 130.26, 'STD': 20599.812016, 'STN': 21.15, 'SVC': 8.749681, 'SYP': 1257.338581, 'SZL': 14.55, 'THB': 32.83, 'TJS': 11.403526, 'TMT': 3.5, 'TND': 2.7835, 'TOP': 2.26685, 'TRY': 8.56599, 'TTD': 6.782808, 'TWD': 28.003002, 'TZS': 2318.681, 'UAH': 27.234774, 'UGX': 3554.501115, 'USD': 1, 'UYU': 43.923162, 'UZS': 10600, 'VES': 3673037.851852, 'VND': 22967.072601, 'VUV': 110.118851, 'WST': 2.551678, 'XAF': 555.889417, 'XAG': 0.03958834, 'XAU': 0.00055415, 'XCD': 2.70255, 'XDR': 0.703965, 'XOF': 555.889417, 'XPD': 0.00037726, 'XPF': 101.127441, 'XPT': 0.00092294, 'YER': 250.200031, 'ZAR': 14.567847, 'ZMW': 22.321753, 'ZWL': 322}

当我运行此代码时，错误是psycopg2.ProgrammingError: can't adapt type 'dict'

提前致谢

标签： pythonjsonpython-3.xdatabasepostgresql