pyodbc无法连接到数据库 [英] pyodbc can't connect to database

问题描述

我正在使用来自

您只能看到1个引擎：

选择该引擎将使我看到 online_banking db

upd1 是通过以下方式创建的：

 创建数据库[online_banking] 
 ON主
（NAME = N'online_banking'，FILENAME = N'C：\ ... \online_banking.mdf'，
 SIZE = 512000KB，MAXSIZE = UNLIMITED，FILEGROWTH = 30％）
登录
（NAME = N'online_banking_log'，FILENAME = N'C：\ ... \online_banking_log.ldf'，
 SIZE = 1024KB，MAXSIZE = 20GB，FILEGROWTH = 10％ ）
 GO 
  

upd2 工具 sqlcmd 。
因此，此 sqlcmd -S（LocalDB）\MSSQLLocalDB -i C：\Users\1.sql -E 显示，
MSSQLLocalDB 没有我的数据库。

但是 sqlcmd -S localhost -i C ：\Users\1.sql -E 成功执行。
我完全感到困惑，我只安装了一台服务器，而且SQL Management Studio看到只有一台本地服务器带有 online_banking 数据库。

尝试在Python中使用此连接字符串

  conn = pyodbc.connect（r'DRIVER = {SQL Server Native Client 11.0};服务器=本地主机;集成安全性= true;数据库= online_banking;自动提交= True'）
  

会导致以下错误：

  pyodbc .Error：（'28000'，'[28000] [Microsoft] [SQL Server Native Client 11.0] [SQL Server] \x ...。（18456）（SQLDriverConnect）; [01S00] [Microsoft] [SQL Server Native Client 11.0] xcd\xe5\xe .... xe8\xff（0）; [28000] [Microsoft] [SQL Server Native Client 11.0] [SQL Server] \xce ... ff （18456）; [01S00] [Microsoft] [SQL Server Native Client 11.0] \xcd\xe ....（0）'
  

upd3：应该附加指定的mdf，得到它：
尝试了几种方法，总是出错（在连接字符串中指定或未指定数据库） ）：

  conn = pyodbc.connect（
 
 r'Driver = { SQL Server Native Client 11.0}；服务器=（localdb）\MSSQLLocalDB;数据库= online_banking; AttachDbFilename = C：\程序文件\Microsoft SQL Server\MSSQL12.SQLSERVERINSAF\MSSQL\DATA\online_banking.mdf; Trusted_Connection =是;集成安全性= true; database = online_banking;’）
 
错误：存在同名数据库，或者无法打开指定文件，或者该文件位于UNC共享上。 
  

我发现，这可能与已附加该数据库的父服务器有关，但未能解决这个问题。

upd4
我尝试了由 online_banking DB

附加

解决方案

事实证明，该数据库位于问题已经附加到本地计算机上的SQL Server的默认实例中，因此连接所需的全部是

  import pyodbc 
 conn_str =（
r Driver = {SQL Server Native Client 11.0}; 
r Server =（local）; 
r Database = online_banking ; 
r Trusted_Connection =是; 
）
 conn = pyodbc.connect（conn_str）
  

主要有两个混淆点：

问：SQL Server默认实例的名称是什么？

A：没有。

在通过名称引用SQL Server实例时，是默认实例只需按计算机名称命名，而命名实例由 MachineName\InstanceName 标识。因此，在名为 PANORAMA

的服务器上，如果我们安装默认实例，则

• 对于SQL Server，我们将其称为 PANORAMA 。


• 如果我们安装名为 SQLEXPRESS的命名实例，则将其称为如 PANORAMA\SQLEXPRESS 。

如果我们指的是SQL Server本地计算机上的实例，我们可以使用（local）代替 PANORAMA 。

Q：（local）和（localdb）表示同一意思吗？

A：否。

（本地）和（本地）\InstanceName 指的是基于服务器的真实实例SQL Server。这些是自SQL Server首次发布以来一直存在的实例。它们作为服务运行，并且能够接受网络连接并完成我们期望数据库服务器执行的所有操作。

（localdb ）和（localdb）\InstanceName 引用–为清晰起见，通常将（localdb）大写为（LocalDB）用于连接到 SQL Server LocalDB实例。这些是主要供开发人员使用的临时本地SQL Server实例。有关详细信息，请参见以下MSDN博客文章：

SQL Express v LocalDB v SQL Compact Edition

I'm using pyodbc library from here and I'm connecting this way:

conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};Server=(localdb)\MSSQLLocalDB;Integrated Security=true; database = online_banking; autocommit = True')

I use MSSQLLocalDBbecause it's the default instance name for SQL Server 2014. And this last version of Python 2.7.

However I cant run any simple query, every if them raise the error, saying that there is no such object or in that particular case database:

cursor.execute('use online_banking;')

The full error:

pyodbc.Error: ('08004', "[08004] [Microsoft][SQL Server Native Client 11.0][SQL Server]Database 'online_banking' does not exist. Make sure that the name is entered correctly. (911) (SQLExecDirectW)")

So what is wrong here?

There is only 1 instance installed and such databases(.mdf)

As you can see only 1 engine:

Selecting that engine will allow me to see online_banking db

upd1 Database've been created this way:

CREATE DATABASE [online_banking]
 ON  PRIMARY 
( NAME = N'online_banking', FILENAME = N'C:\...\online_banking.mdf' , 
  SIZE = 512000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30%)
 LOG ON 
( NAME = N'online_banking_log', FILENAME = N'C:\...\online_banking_log.ldf' , 
  SIZE = 1024KB , MAXSIZE = 20GB , FILEGROWTH = 10%)
GO

upd2 I've used built-in tool sqlcmd. So this sqlcmd -S (LocalDB)\MSSQLLocalDB -i C:\Users\1.sql -E have shown, that MSSQLLocalDB doesn't have my database.

However sqlcmd -S localhost -i C:\Users\1.sql -E performed successfully. I'm totally confused, I' ve installed only one server, moreover SQL Management studio sees only one local server with my online_banking DB. This is look really weird to me.

Trying to use this connection string in Python

conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};Server=localhost;Integrated Security=true; database = online_banking; autocommit = True')

causes the error below:

pyodbc.Error: ('28000', '[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]\x... "". (18456) (SQLDriverConnect); [01S00] [Microsoft][SQL Server Native Client 11.0]\xcd\xe5\xe....xe8\xff (0); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]\xce...ff "". (18456); [01S00] [Microsoft][SQL Server Native Client 11.0]\xcd\xe.... (0)'

upd3: Specified mdf should be attached, got it: Tried several ways, always errors (with database specified or not in connection string):

conn = pyodbc.connect( 

       r'Driver={SQL Server Native Client 11.0};Server=(localdb)\MSSQLLocalDB; database =online_banking; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVERINSAF\MSSQL\DATA\online_banking.mdf;Trusted_Connection=Yes; Integrated Security=true; database = online_banking;')

error: A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I found out, that may be related with parent server which already have attached this db, but failed to solve this.

upd4 I tried simple code from here to see if "online_banking" shows up in the list of databases for that instance. But faced another error:

pyodbc.Error: ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]\ - unreadable error

In addition that database according to SSMS seems have already attached by online_banking DB

解决方案

As it turns out, the database in question was already attached to the default instance of SQL Server on the local machine, so all that was needed to connect was

import pyodbc
conn_str = (
    r"Driver={SQL Server Native Client 11.0};"
    r"Server=(local);"
    r"Database=online_banking;"
    r"Trusted_Connection=yes;"
    )
conn = pyodbc.connect(conn_str)

There were two main points of confusion:

Q: What is the name of a SQL Server "default instance"?

A: It doesn't have one.

When referring to a SQL Server instance by name, a default instance simply goes by the name of the machine, while a named instance is identified by MachineName\InstanceName. So, on a server named PANORAMA

• If we install a "default instance" of SQL Server we refer to it as PANORAMA.
• If we install a "named instance" called "SQLEXPRESS" we refer to it as PANORAMA\SQLEXPRESS.

If we are referring to a SQL server instance on the local machine we can use (local) instead of PANORAMA.

Q: Do (local) and (localdb) mean the same thing?

A: NO.

(local) and (local)\InstanceName refer to "real" server-based instances of SQL Server. These are the instances that have been around since SQL Server was first released. They run as a service and are able to accept network connections and do all of the the things we expect a database server to do.

(localdb) and (localdb)\InstanceName references – with (localdb) usually capitalized as (LocalDB) for clarity – are used to connect to "SQL Server LocalDB" instances. These are temporary local SQL Server instances primarily intended for developers. For details see the following MSDN blog post:

SQL Express v LocalDB v SQL Compact Edition

这篇关于pyodbc无法连接到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！