将Python与SQL Server数据库连接

When I am trying to connect python with SQL Server, following error occurred.

当我尝试将python与SQL Server连接时,发生以下错误。

"pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)')"

“pyodbc.Error:('08001','[08001] [Microsoft] [ODBC SQL Server驱动程序] [DBNETLIB] SQL Server不存在或访问被拒绝。(17)(SQLDriverConnect)')”

Following is the my code.

以下是我的代码。

import pyodbc
connection = pyodbc.connect("Driver={SQL Server}; Server=localhost;          
Database=emotionDetection; uid=uname ;pwd=pw;Trusted_Connection=yes")
cursor = connection.cursor()
SQLCommand = ("INSERT INTO emotion"  "(happy, sad, angry) "
          "VALUES (?,?,?)")
Values = ['smile','cry','blame']
cursor.execute(SQLCommand,Values)
connection.commit()
connection.close()

This is my first attempt to connect Python with sql server. I don't have an idea what would be the driver name, server name, username and password.Do you have any idea of what should be my configuration. Please help me.

这是我第一次尝试将Python与sql server连接起来。我不知道驱动程序名称,服务器名称,用户名和密码是什么。你知道我的配置应该是什么。请帮帮我。

3 个解决方案

#1

CONNECTION FROM WINDOWS TO MS SQL SERVER DATABASE:

Here you have an example I use myself to connect to MS SQL database table with a Python script:

这里有一个例子,我用自己用Python脚本连接到MS SQL数据库表:

import pyodbc
server = 'ip_database_server'
database = 'database_name'
username = 'user_name'
password = 'user_password'
driver = '{SQL Server}' # Driver you need to connect to the database
port = '1433'
cnn = pyodbc.connect('DRIVER='+driver+';PORT=port;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+
                 ';PWD='+password)
cursor = cnn.cursor()

'User' and 'password' and 'table_name' are attibutes defined by the DB administrator, and he should give them to you. The port to connect to is also defined by the admin. If you are trying to connect from a Windows device to the DB, go to ODBC Data Source Administrator from Windows, and check if you have installed the driver:

“用户”,“密码”和“table_name”是数据库管理员定义的属性,他应该将它们提供给您。要连接的端口也由管理员定义。如果您尝试从Windows设备连接到数据库,请从Windows转到ODBC数据源管理器,并检查是否已安装驱动程序:

Where is the ODBC data source administrator in a Windows machine.

Windows计算机中的ODBC数据源管理员在哪里。

The image is in spanish, but you only have to click on 'Drivers' tab, and check if the driver is there as in the image.

图像是西班牙语,但您只需单击“驱动程序”选项卡,然后检查驱动程序是否在图像中。

CONNECTION FROM LINUX/UNIX TO MS SQL SERVER DATABASE:

If you are working in Linux/Unix, then you shoud install a ODBC manager like 'FreeTDS' and 'unixODBC'. To configure them, you have some examples in the following links:

如果你在Linux / Unix上工作,那么你应该安装一个像'FreeTDS'和'unixODBC'这样的ODBC管理器。要配置它们,您可以在以下链接中找到一些示例:

Example: Connecting to Microsoft SQL Server from Linux/Unix

示例:从Linux / Unix连接到Microsoft SQL Server

Example: Installing and Configuring ODBC

示例:安装和配置ODBC

#2

I think you should check out this. * answer about odbc

我想你应该看看这个。 *回答关于odbc

Also, what sql server do you use?

另外,你使用什么SQL服务器?

#3

Working Examples Work Best For Me:

Need Mac ODBC Drivers?

If you need the mac driver I used homebrew and found the commands here

如果您需要我使用自制软件的mac驱动程序并在此处找到命令

Detail

I personally learn best by reverse enginerring, with that said I am sharing one of my examples, it may be a bit crude but I'm growing my Python skills.

我个人通过反向工程学习最好,据说我分享了我的一个例子,它可能有点粗糙,但我正在增长我的Python技能。

My script I created allows me to connect my Mac OS to a AWS RDS instance.

我创建的脚本允许我将Mac OS连接到AWS RDS实例。

The whole script is a copy paste with a little modification for you about your server info, and you are off and running. just modify these lines to connect.

整个脚本是一个复制粘贴,对您的服务器信息进行了一些修改,您即可开始运行。只需修改这些行即可连接。

server = 'yourusername'
username = 'yourusername'
password = 'yourforgottencomplicatedpassword'
database = 'yourdatabase'

Then Run the file: python3 ~/Your/path/pyodbc_mssqldbtest.py and you should be set.

然后运行文件: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://*.com/q/12704305/1896134
# Answer to an SO question: https://*.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()