第八课: - 从Microsoft SQL数据库读取
第 8 课
如何从Microsoft SQL数据库中提取数据
In [1]:
# Import libraries
import pandas as pd
import sys
from sqlalchemy import create_engine, MetaData, Table, select, engine
In [2]:
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)
版本1
在本节中,我们使用sqlalchemy库从sql数据库中获取数据。确保使用您自己的ServerName,Database,TableName。
In [3]:
# Parameters
TableName = "data"
DB = {
'drivername': 'mssql+pyodbc',
'servername': 'DAVID-THINK',
#'port': '5432',
#'username': 'lynn',
#'password': '',
'database': 'BizIntel',
'driver': 'SQL Server Native Client 11.0',
'trusted_connection': 'yes',
'legacy_schema_aliasing': False
}
# Create the connection
engine = create_engine(DB['drivername'] + '://' + DB['servername'] + '/' + DB['database'] \
+ '?' + 'driver=' + DB['driver'] + ';' + 'trusted_connection=' + DB['trusted_connection'], \
legacy_schema_aliasing=DB['legacy_schema_aliasing'])
conn = engine.connect()
# Required for querying tables
metadata = MetaData(conn)
# Table to query
tbl = Table(TableName, metadata, autoload=True, schema="dbo")
#tbl.create(checkfirst=True)
# Select all
sql = tbl.select()
# run sql code
result = conn.execute(sql)
# Insert to a dataframe
df = pd.DataFrame(data=list(result), columns=result.keys())
# Close connection
conn.close()
print('Done')
选择数据帧中的内容。
In [4]:
df.head()
Out[4]:
In [5]:
df.dtypes
Out[5]:
转换为特定的数据类型。下面的代码必须修改成符合你的表。
版本 2
In [6]:
import pandas.io.sql
import pyodbc
In [7]:
# Parameters
server = 'DAVID-THINK'
db = 'BizIntel'
# Create the connection
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + DB['servername'] \
+ ';DATABASE=' + DB['database'] + ';Trusted_Connection=yes')
# query db
sql = """
SELECT top 5 *
FROM data
"""
df = pandas.io.sql.read_sql(sql, conn)
df.head()
Out[7]:
版本 3
In [8]:
from sqlalchemy import create_engine
In [9]:
# Parameters
ServerName = "DAVID-THINK"
Database = "BizIntel"
Driver = "driver=SQL Server Native Client 11.0"
# Create the connection
engine = create_engine('mssql+pyodbc://' + ServerName + '/' + Database + "?" + Driver)
df = pd.read_sql_query("SELECT top 5 * FROM data", engine)
df
Out[9]:
This tutorial was rewrited by CDS.