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) enter image description here enter image description here

As you can see only 1 engine:

enter image description here

Selecting that engine will allow me to see online_banking db enter image description here

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]\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 enter image description here according to SSMS seems have already attached by online_banking DB

• Gord Thompson: SQL Server LocalDB can have any number of named instances. Try the test code here and see if "online_banking" shows up in the list of databases.
• Gord Thompson: From the path to the .mdf file it looks like the online_banking database was created in a "regular" (not "LocalDB") SQL Server instance named SQLSERVERINSAF. Try the sample code here to see if "online_banking" shows up in the list of databases for that instance. If not, then open (local)\SQLSERVERINSAF in SQL Server Management Studio (SSMS) and Attach it.
• Gord Thompson: Start the SQL Server Browser service. Launch SQL Server Management Studio. In the "Connect to Server" dialog, click the "Server name" drop-down and choose "<Browse for more...>" (screenshot). On the "Local Servers" tab of the "Browse for Servers" dialog, click the "+" beside "Database Engine". That should show you a list of local server instances. Mine looks like this. Please edit your question to show yours.
• Gord Thompson: Great! Now try the test code here and see if you can connect.
• Rocketq: @GordThompson This is magic)) Works as should. I just inserted values, thank you a lot. And don't forget to post smth as a answer to get 50 points))

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 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

• RoMEoMusTDiE: Ideally you should pud the machine name rather local if deployed for multi user.

Answer 2

It could possibly be a security issue. You are using integrated security so it will use the security credentials of the windows login that the client program is running. If that user or a group that the user belongs to does not have at least public access to the database, it will appear as if the database does not exist. Either ensure that the user or a group that the user is a member of is set up with a login and that it has at least public access to your database, or use SQL server authentication and send a username and password in your connection string.

• Rocketq: I don't think that problem related with access problem. My PC has only one account and its me, and I'm the admin. Or am I should run PyCharm as admin?