如何用 Python 連結 PostgreSQL
先附上我之前怎麼用 python 連結 postgresql 的 原始碼，程式很容易理解，原則上要先安裝 psycopg2 這個 library，就能成功 select 到 test 這個 table 的資料
import psycopg2
# 測試連線 PostgreSQL
database = "havocfuture"
dbUser = "havocfuture"
dbPwd = "xxxxxxxx"
conn = psycopg2.connect(database=database, user=dbUser, password=dbPwd, host="127.0.0.1", port="5432")
print("opened database successfully")
# select資料
cur = conn.cursor()
cur.execute("select * from test")
rows = cur.fetchall()
for row in rows:
print ("row0:", row[0])
print ("row1:", row[1])
print("select data done successfully")
# close connection
conn.close()
如何在 pandas.dataframe.to_sql 寫入 PostgreSQL
pandas.dataframe 的 to_sql 是很好用的 function，但是在 postgresql 卻不能用，測試程式如下
from FinMind.data import DataLoader
import pandas as pd
import psycopg2
# 取得 dataframe 資料
api = DataLoader()
df = api.taiwan_stock_info()
print(df)
# 測試連線 PostgreSQL
database = "havocfuture"
dbUser = "havocfuture"
dbPwd = "xxxxxxxx"
conn = psycopg2.connect(database=database, user=dbUser, password=dbPwd, host="127.0.0.1", port="5432")
print("opened database successfully")
df.to_sql('taiwan_stock_info', conn)
print("insert data successfully")
conn.close()
但這時候卻出現錯誤
DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': relation "sqlite_master" does not exist
LINE 1: SELECT name FROM sqlite_master WHERE type='table' AND name=?...
查了一下才發現 pandas.dataframe.to_sql 不支援 PostgreSQL，只支援 MySQL 和 SQLite，如果要在 PostgreSQL 使用要把 psycopg2 換成 sqlalchemy，修改後程式碼如下
from FinMind.data import DataLoader
import pandas as pd
from sqlalchemy import create_engine
# 取得 dataframe 資料
api = DataLoader()
df = api.taiwan_stock_info()
print(df)
# 測試連線 PostgreSQL
database = "havocfuture"
dbUser = "havocfuture"
dbPwd = "xxxxxxxx"
engine = create_engine('postgresql://'+ dbUser +':'+ dbPwd +'@localhost:5432/' + database)
print("opened database successfully")
df.to_sql('taiwan_stock_info', engine)
print("insert data successfully")
engine.dispose()
成功將資料寫入 DB，問題解決
