| 354 | |
| 355 | |
| 356 | def insert_pg(df, tableName, engine): |
| 357 | # fix field type |
| 358 | for i in range(len(df.dtypes)): |
| 359 | if str(df.dtypes[i]) == 'uint64': |
| 360 | df[str(df.columns[i])] = df[str(df.columns[i])].astype('string') |
| 361 | |
| 362 | conn = engine.raw_connection() |
| 363 | cursor = conn.cursor() |
| 364 | try: |
| 365 | df.to_sql( |
| 366 | tableName, |
| 367 | engine, |
| 368 | if_exists='replace', |
| 369 | index=False |
| 370 | ) |
| 371 | # trans csv |
| 372 | output = StringIO() |
| 373 | df.to_csv(output, sep='\t', header=False, index=False) |
| 374 | # output.seek(0) |
| 375 | |
| 376 | # pg copy |
| 377 | query = sql.SQL("COPY {} FROM STDIN WITH CSV DELIMITER E'\t'").format( |
| 378 | sql.Identifier(tableName) |
| 379 | ) |
| 380 | cursor.copy_expert(sql=query.as_string(cursor.connection), file=output) |
| 381 | conn.commit() |
| 382 | except Exception as e: |
| 383 | traceback.print_exc() |
| 384 | raise HTTPException(400, str(e)) |
| 385 | finally: |
| 386 | cursor.close() |
| 387 | conn.close() |
| 388 | |
| 389 | |
| 390 | t_sheet = "数据表列表" |