python - pyodbc can't connect to database

Question

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:Users1.sql -E have shown, that MSSQLLocalDB doesn't have my database.

However sqlcmd -S localhost -i C:Users1.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]xcdxe5xe....xe8xff (0); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]xce...ff "". (18456); [01S00] [Microsoft][SQL Server Native Client 11.0]xcdxe.... (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 FilesMicrosoft SQL ServerMSSQL12.SQLSERVERINSAFMSSQLDATAonline_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

Answer 1

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 MachineNameInstanceName. 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 PANORAMASQLEXPRESS.

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