本问参照的是作者 Vic Liu书写的查询脚本,其博客为:https://blog.csdn.net/upluck
准备工作
使用到的包:
- pymssql
- sqlalchemy :Object Relational Mapping(对象关系映射)工具
- pandas
导入包
import pymssql
from sqlalchemy import create_engine
import pandas as pd
from sqlalchemy.sql import text as sql_text
实际操作
创建链接
def get_engine(db_user,db_password,db_host,db_name):
str_format = 'mssql+pymssql://{0}:{1}@{2}/{3}?charset=utf8'
connection_str = str_format.format(db_user,db_password,db_host,db_name)
engine = create_engine(connection_str,echo=False)
return engine
# db_user:User Name;str_format{0}位置;字符串类型''
# db_password:User Password;str_format{1}位置;字符串类型''
# db_host:Database host -> Server Name;str_format{2}位置;字符串类型''
# db_name:Database Name;str_format{3}位置;字符串类型''
SQL查询数据导入到Dataframe
def read_data(sql):
engine = get_engine()
df = pd.read_sql(sql, con=engine)
return df
# get_engine():上面的get_engine函数
# read_sql:函数执行sql查询语句,并导入sql结果去dataframe
# sql:需要是字符串类型:不需要换行'sql';需要换行'''sql'''