一、Python连接MySQL数据库及模块封装
1、连接模块封装
步骤:首先建立名为“db_sql” 的文件夹,在文件夹下再建立名为“db_mysql”的“.py"文件,在改文件下编辑如下代码”:
import pymysql
import pandas as pd
def execute_sql(sql):
localhost = "72.106.100.12"
user = "anchangkong"
pwd = "666666"
try:
db = pymysql.connect(localhost,user,pwd,charset='utf8')
except:
print('连接数据库失败,请检查连接条件')
cursor = db.cursor()
try:
cursor.execute(sql)
except:
print('查询失败,请检查sql')
results = cursor.fetchall()
col = cursor.description
data = pd.DataFrame(list(results),columns = pd.DataFrame(list(col))[0])
db.close()
return data
if __name__ == '__main__':
sql = "select * from ems_website.aws_send_dresslily limit 20"
data = execute_sql(sql)
print(data)
2、连接模块使用
步骤:首先在建立的名为“db_sql” 的文件夹同级目录下新建py文件(pycharm打开)或ipynb文件(jupyter打开),再打开文件编辑如下代码”:
from db_sql import db_mysql
sql = "select * from ems_website.aws_send_dresslily limit 20"
data = db_mysql.execute_sql(sql)
data
data输出为
二、Python连接SQL Server数据库及模块封装
1、连接模块封装
步骤:首先建立名为“db_sql_server” 的文件夹,在文件夹下再建立名为“db_sql_svr”的“.py"文件,在改文件下编辑如下代码”:
import pymssql
import pandas as pd
def execute_sql(sql):
localhost = "10.10.1.99"
user = "anchangkong"
pwd = "666666"
try:
db = pymssql.connect(localhost,user,pwd,charset='utf8')
except:
print('连接数据库失败,请检查连接条件')
cursor = db.cursor()
try:
cursor.execute(sql)
except:
print('查询失败,请检查sql')
results = cursor.fetchall()
col = cursor.description
data = pd.DataFrame(list(results),columns = pd.DataFrame(list(col))[0])
db.close()
return data
if __name__ == '__main__':
sql = "select top 5 ORDER_SN from db_sync.dbo.tb_eload_order_info "
data = execute_sql(sql)
print(data)
2、连接模块使用
步骤:首先在建立的名为“db_sql_server” 的文件夹同级目录下新建py文件(pycharm打开)或ipynb文件(jupyter打开),再打开文件编辑如下代码”:
from db_sql_server import db_sql_svr
sql = "select top 5 ORDER_SN from db_sync.dbo.tb_eload_order_info "
data = db_sql_svr.execute_sql(sql)
data