Python加载csv到postgres表列，严格格式

Question

我的postgres数据库中有这个表

+------------+------------+----------------+----------+-------------------------+
|  recvtime  | entitytype |    attrname    | attrtype |        attrvalue        |
+------------+------------+----------------+----------+-------------------------+
| 2019-05-27 | Noise      | measurand      | Number   | 51.7                    |
| 2019-05-27 | Noise      | sonometerClass | Text     | 1                       |
| 2019-05-27 | Noise      | name           | Text     | City Centre             |
| 2019-05-27 | Noise      | longitude      | Number   | -8.65915                |
| 2019-05-27 | Noise      | latitude       | Number   | 41.1591                 |
| 2019-05-27 | Noise      | dateObserved   | DateTime | 2016-05-24T18:38:15.00Z |
+------------+------------+----------------+----------+-------------------------+

然后希望读取此csv文件的内容：

measurand,sonometerClass,name,longitude,latitude,dateObserved
90.4,1,Hospital de S. Joao,-8.603257,41.183778,2014-07-12T6:18:15.00Z
59.3,0,City Campus,-8.594866,41.178031,2014-08-12T16:10:10.00Z
64.5,1,ABC Beach,-8.607085,41.15001,2015-10-11T16:10:10.00Z

目标是以attrname列始终位于文件头的方式从文件中读取数据，而对于每一行，数据都加载到attrvalue列，以便：

对于第1行：

90.4,1,Hospital de S. Joao,-8.603257,41.183778,2014-07-12T6:18:15.00Z

我得到以下场景：

measurand = 90.4
sonometerClass = 1
name = Hospital de S. Joao
longitude = -8.603257
latitude = 41.183778
dateObserved = 2014-07-12T6:18:15.00Z

同样，对于第2行：

59.3,0,City Campus,-8.594866,41.178031,2014-08-12T16:10:10.00Z

我得到以下信息：

measurand = 59.3
sonometerClass = 0
name = Campus
longitude = -8.594866
latitude = 41.178031
dateObserved = 2014-07-12T6:18:15.00Z

所有其他列recvtime, entitytype和attrtype保持不变（重复）

Python脚本：

import psycopg2
import sys

try:
    conn = psycopg2.connect(host="localhost", dbname="postgres", \
      user="postgres", password="password")
    print('Connecting to the Database...')
    cur = conn.cursor()
    with open('noise2.csv', 'r') as f:
        next(f)
        cur.copy_from(f, 'urbansense.basic_004_noise', sep=',')
    conn.commit()

except Exception as e:
    print('Error: {}'.format(str(e)))
    sys.exit(1)

错误：

$python3 readcsv.py
Connecting to the Database...
Error: invalid input syntax for integer: "63.4"
CONTEXT:  COPY basic_004_noise, line 1, column recvtimets: "63.4"

我如何实现我的目标？

Answer 1

import csv
csvfile = "your/path/to/csv/file.csv"
with open(csvfile) as cf:
  reader = csv.DictReader(cf, delimiter=',',quotechar='"')
  for row in reader:
     measurand = float(row["measurand"]) # float
     sonometerClass = int(row["sonometerClass"])  # int
     name = row["name"]  # str
     longitude = float(row["longitude"])  # float
     latitude = float(row["latitude"])  # float
     dateObserved = row["dateObserved"]  # str

     # your code for postgres database here