如何用 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，问题解决
