Python 连接MS SQL和Oracle数据库

DataArchitect 2021-08-16

1296

本文将介绍如何使用Python连接SQL Server及Oracle数据库，并将返回结果转换为Pandas DataFrame格式，便于后期数据分析。

1. Python连接SQL Server

1.1 安装pyodbc库

pyodbc is an open source Python module that makes accessing ODBC databases simple.

pyodbbc官方文档：https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver15

pip install pyodbc

1.2 完整代码

# import packages
import pandas as pd
import pyodbc


# set connection parameters
server = 'abc.xxx.com\SQLEXPRESS' 
database = 'mydatabase' 
username = 'myuser' 
password = 'mypassword' 


CONN = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
SQL = 'SELECT * FROM [mydatabase].[dbo].[mytable]'
# query in database and return DataFrame
df = pd.read_sql_query(SQL,CONN)

2. Python连接Oracle数据库

2.1 安装cx_Oracle库

cx_Oracle is a Python extension module that enables access to Oracle Database.

cx_Oracle官方文档：https://oracle.github.io/python-cx_Oracle/

安装过程可能会遇到版本兼容问题，参见官方文档：https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html

注意：Oracle Instance Client的版本须于cx_Oracle版本对应，如都为64位软件。

pip install cx_Oracle

2.2 完整代码

# import packages
import cx_Oracle
import pandas as pd


# set connection parameters
host= "myserver"
port = 38000
service_name = "myservicename"
username = "myusername"
password = "mypassword"


dsn = cx_Oracle.makedsn(host=host, port=port, service_name=service_name)
CONN = cx_Oracle.connect(user=username, 
                         password=password, 
                         dsn=dsn,
                         encoding="UTF-8")


SQL = 'select * from mytable'
df = pd.read_sql_query(SQL,CONN)

oracle python

文章转载自DataArchitect，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Python 连接MS SQL和Oracle数据库

评论