第八课: - 从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__) 
Python version 3.5.1 |Anaconda custom (64-bit)| (default, Feb 16 2016, 09:49:46) [MSC v.1900 64 bit (AMD64)]
Pandas version 0.20.1 

版本1

在本节中,我们使用sqlalchemy库从sql数据库中获取数据。确保使用您自己的ServerNameDatabaseTableName

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')
 
Done
 选择数据帧中的内容。
In [4]:
df.head()
Out[4]:
 DateSymbolVolume
0 2013-01-01 A 0.00
1 2013-01-02 A 200.00
2 2013-01-03 A 1200.00
3 2013-01-04 A 1001.00
4 2013-01-05 A 1300.00
In [5]:
df.dtypes
Out[5]:
Date      datetime64[ns]
Symbol            object
Volume            object
dtype: object
 

转换为特定的数据类型。下面的代码必须修改成符合你的表。

 

版本 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]:
 DateSymbolVolume
0 2013-01-01 A 0.0
1 2013-01-02 A 200.0
2 2013-01-03 A 1200.0
3 2013-01-04 A 1001.0
4 2013-01-05 A 1300.0
 

版本 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]:
 DateSymbolVolume
0 2013-01-01 A 0.0
1 2013-01-02 A 200.0
2 2013-01-03 A 1200.0
3 2013-01-04 A 1001.0
4 2013-01-05 A 1300.0
 

This tutorial was rewrited by CDS.

posted on 2018-05-22 15:31  六尺巷人  阅读(269)  评论(0编辑  收藏  举报

导航