python与sql数据库_将Python与SQL Server数据库连接

工作实例对我最有用：

需要Mac ODBC驱动程序吗？

如果你需要我使用的mac驱动程序homebrew和found the commands here

细节

我个人通过逆向工程学习得最好，也就是说我正在分享我的一个例子，它可能有点粗糙，但我正在提高我的Python技能。

My script I created允许我将Mac OS连接到AWS RDS实例。

整个脚本是一个拷贝粘贴，对你的服务器信息稍加修改，你就可以关闭并运行了。

只需修改这些线来连接。server = 'yourusername'

username = 'yourusername'

password = 'yourforgottencomplicatedpassword'

database = 'yourdatabase'

然后运行文件：python3 ~/Your/path/pyodbc_mssqldbtest.py，您应该被设置。#!/usr/bin/env python3

# -*- coding: utf-8 -*-

# =============================================================================

# Created By : Jeromie Kirchoff

# Created Date: Mon July 31 22:32:00 PDT 2018

# FILENAME: pyodbc_mssqldbtest.py

# =============================================================================

"""The Module Has Been Build for Interaction with MSSQL DBs To Test the con."""

# =============================================================================

# Thanks to this post for headers https://stackoverflow.com/q/12704305/1896134

# Answer to an SO question: https://stackoverflow.com/q/42433408/1896134

# =============================================================================

import pyodbc

def runningwithqueries(query):

"""The Module Has Been Build to {Open, Run & Close} query connection."""

print("\nRunning Query: " + str(query) + "\nResult :\n")

crsr = cnxn.execute(query)

columns = [column[0] for column in crsr.description]

print(columns)

for row in crsr.fetchall():

print(row)

crsr.close()

# =============================================================================

# SET VARIABLES NEEDED FOR SERVER CONNECTION

# =============================================================================

server = 'yourusername'

username = 'yourusername'

password = 'yourforgottencomplicatedpassword'

database = 'yourdatabase'

connStr = (r'DRIVER={ODBC Driver 17 for SQL Server};' +

r"Integrated Security=True;" +

r'SERVER=' + server +

r';UID=' + username +

r';PWD=' + password +

r';DSN=MSSQL-PYTHON' +

r';DATABASE=' + database + ';'

)

print("Your Connection String:\n" + str(connStr) + "\n\n")

# =============================================================================

# CONNECT TO THE DB

# =============================================================================

cnxn = pyodbc.connect(connStr, autocommit=True)

# =============================================================================

# SET QUERIES TO VARIABLES

# =============================================================================

SQLQUERY1 = ("SELECT @@VERSION;")

SQLQUERY2 = ("SELECT * FROM sys.schemas;")

SQLQUERY3 = ("SELECT * FROM INFORMATION_SCHEMA.TABLES;")

SQLQUERY4 = ("SELECT * FROM INFORMATION_SCHEMA.COLUMNS;")

SQLQUERY5 = ("SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;")

SQLQUERY6 = ("EXEC sp_databases;")

SQLQUERY7 = ("EXEC sp_who2 'active';")

# =============================================================================

# RUN QUERIES

# YOU CAN RUN AS MANY QUERIES AS LONG AS THE CONNECTION IS OPEN TO THE DB

# =============================================================================

runningwithqueries(SQLQUERY1)

runningwithqueries(SQLQUERY2)

runningwithqueries(SQLQUERY3)

runningwithqueries(SQLQUERY4)

runningwithqueries(SQLQUERY5)

runningwithqueries(SQLQUERY6)

runningwithqueries(SQLQUERY7)

# =============================================================================

# CLOSE THE CONNECTION TO THE DB

# =============================================================================

cnxn.close()