python - 无法通过 Sqlalchemy 设置主键，但可以通过 PGAdmin4

Question

对于以下数据框：

      LAD20CD                    LAD20NM   BNG_E   BNG_N  ...        LAT   Shape__Area  Shape__Length                                           geometry
0   E07000154                Northampton  476495  260539  ...  52.237751  8.255064e+07   38381.688084  POLYGON ((-0.8091414142605670 52.2753276939684...
1   E07000246  Somerset West and Taunton  304960  130228  ...  51.063480  1.191178e+09  233156.429712  POLYGON ((-3.0538047490802600 51.2059417666536...
2   E07000040                 East Devon  313790   96050  ...  50.757599  8.182959e+08  169999.596103  MULTIPOLYGON (((-3.0524230989883701 50.9082640...
3   E07000044                 South Hams  270676   54036  ...  50.371948  8.921215e+08  234574.690559  POLYGON ((-3.5842498548751598 50.4777231181161...
4   E07000202                    Ipswich  617161  244456  ...  52.055920  4.084468e+07   29187.875675  POLYGON ((1.1578388391924299 52.08875163594530...
5   E06000026                   Plymouth  249945   58255  ...  50.404942  8.288777e+07   49419.795939  POLYGON ((-4.1230475222729899 50.3467427583020...
6   E07000079                   Cotswold  402125  208209  ...  51.772549  1.167649e+09  275881.531075  POLYGON ((-1.6657543045486300 51.9874888219864...
7   E08000002                       Bury  379658  410768  ...  53.593102  1.007527e+08   57024.343964  POLYGON ((-2.2717870687905002 53.6145142332618...
8   E07000084      Basingstoke and Deane  454508  151423  ...  51.259369  6.345992e+08  122971.049819  POLYGON ((-0.9861237505300590 51.3628482885656...
9   E07000078                 Cheltenham  394925  222232  ...  51.898609  4.653884e+07   31000.684891  POLYGON ((-2.0102151915442801 51.9029244535680...
10  E07000126               South Ribble  352017  425840  ...  53.726749  1.151752e+08   66247.390716  POLYGON ((-2.5994877797848099 53.7814710235385...
11  E08000037                  Gateshead  420168  559658  ...  54.931198  1.475563e+08   67934.528110  POLYGON ((-1.7697567363655600 54.9809837372463...
12  E07000068                  Brentwood  558560  196070  ...  51.641079  1.530372e+08   62499.674509  POLYGON ((0.4023278825251010 51.65099490683400...
13  E08000026                   Coventry  432807  279689  ...  52.414230  9.979901e+07   43481.405727  POLYGON ((-1.4590531741648900 52.4551580337384...
14  S12000029          South Lanarkshire  284634  636071  ...  55.604530  1.771616e+09  247590.081941  POLYGON ((-4.1070317994739796 55.8346525858565...
15  E07000029                   Copeland  310871  508739  ...  54.466171  7.405896e+08  142439.232915  POLYGON ((-3.1671393240152499 54.4541106699468...
16  E08000034                   Kirklees  414586  416223  ...  53.642330  4.053064e+08  106837.808449  POLYGON ((-1.6816208841975799 53.7564689245214...
17  E06000017                    Rutland  492992  308655  ...  52.667648  3.921855e+08   96395.318751  POLYGON ((-0.4950258021289160 52.6402363852470...
18  E07000121                  Lancaster  356896  464988  ...  54.079010  5.801983e+08  167797.392829  POLYGON ((-2.4608627348339200 54.2267161360627...
19  E08000025                 Birmingham  408150  287352  ...  52.484039  2.690266e+08   88776.343219  POLYGON ((-1.7880812993329001 52.5878626088220..

我可以使用以下代码成功地将数据框保存到 Postgres：

# CONNECT TO POSTGRES.
conn_params_dict = {"user":"postgres",
                    "password":"postgres",
                    # FOR host, USE THE POSTGRES INSTANCE CONTAINER NAME, AS THE CONTAINER IP CAN CHANGE.
                    "host":"postgres",
                    "database":"github_projects"}

connect_alchemy = "postgresql+psycopg2://%s:%s@%s/%s" % (
    conn_params_dict['user'],
    conn_params_dict['password'],
    conn_params_dict['host'],
    conn_params_dict['database']
)

# CREATE POSTGRES ENGINE (CONNECTION POOL).
engine = create_engine(connect_alchemy)

# CONVERT geometry COLUMN FROM DTYPE geometry TO DTYPE object TO ALLOW DATAFRAME TO BE SAVED TO POSTGRES.
lad_gdf['geometry'] = lad_gdf['geometry'].apply(lambda x: wkt.dumps(x))

pd.DataFrame(lad_gdf).to_sql("shapefile_lad20", con = engine, if_exists='replace', index=True,
                             dtype={"lad20code":sqlalchemy.types.Text,
                                    "lad20nm":sqlalchemy.types.Text,
                                    "bng_e":sqlalchemy.types.Integer,
                                    "bng_n":sqlalchemy.types.Integer,
                                    "long":sqlalchemy.types.Float,
                                    "lat":sqlalchemy.types.Float,
                                    "shape__area":sqlalchemy.types.Float,
                                    "shape__length":sqlalchemy.types.Float,
                                    "geometry":sqlalchemy.types.Text
                                     })

然后我尝试使用以下设置主键：

set_primary_key = engine.execute("""
                                 ALTER TABLE shapefile_lad20 ADD PRIMARY KEY (lad20cd)
                                 """)
set_primary_key.close()

但这失败并给出错误：

ProgrammingError: (psycopg2.errors.UndefinedColumn) column "lad20cd" of relation "shapefile_lad20" does not exist

该lad20cd属性确实存在。我尝试将案例更改为以防LAD20CD万一，但我得到了相同的结果。

奇怪的是，我可以LAD20CD通过 PGAdmin4 GUI 设置为主键，所以我不确定这里有什么问题？

我必须将geometry列从转换dtype = geometry为，dtype = object以便我可以将数据框保存到 Postgres - 这一步可能是原因吗？

谢谢

标签： pythonpandaspostgresqlsqlalchemyprimary-key