pyodbc connection error when trying to connect to DB on localhost

Question

I have a local DB on my machine called 'Test' which contains a table called 'Tags'. I am able to access this DB and query from this table through SQL Server management studio 2008.

However, when using pyodbc I keep running into problems.

Using this:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost:1433;DATABASE=Test')

yields the error:

pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnectW); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()). (14)')

(with or without specifying the port)

Trying an alternative connection string:

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost\Test,1433')

yields no error, but then:

cur = conn.cursor()
cur.execute("SELECT * FROM Tags")

yields the error:

pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Tags'. (208) (SQLExecDirectW)")

Why could this be?

Well, I copied this DB from an external server, and when I tried to connect to that server, this connection string (without port specification) worked. i.e., "DRIVER={SQL Server};SERVER=DB-TEST-SRV;DATABASE=Test" yielded no error, and querying in the way I specified above yielded the expected result with no error. — tafi
– tafi, Commented Oct 3, 2011 at 7:50

Rob Hruska · Accepted Answer · 2012-07-12 20:12:36Z

5

I tried changing your query to

SELECT * FROM Test.dbo.Tags

and it worked.

edited Jul 12, 2012 at 20:12

Rob Hruska

121k32 gold badges170 silver badges193 bronze badges

answered Jul 12, 2012 at 15:50

Wayne

511 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bryan · Accepted Answer · 2011-10-03 15:05:21Z

3

I don't see any authentication attributes in your connection strings. Try this (I'm using Windows authentication):

conn = pyodbc.connect('Trusted_Connection=yes', driver = '{SQL Server}',
                      server = 'localhost', database = 'Test')
cursor = conn.cursor()
# assuming that Tags table is in dbo schema
cursor.execute("SELECT * FROM dbo.Tags")

answered Oct 3, 2011 at 15:05

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Comments

David Doyle · Accepted Answer · 2017-09-23 18:01:04Z

2

Try replacing 'localhost' with either '(local)' or '.'. This solution fixed the problem for me.

answered Sep 23, 2017 at 18:01

David Doyle

211 bronze badge

1 Comment

Amirali Samiei Over a year ago

for me "." worked, but 'localhost' or 'local' didn't.

zx485 · Accepted Answer · 2018-10-05 19:39:47Z

1

For me, apart from maintaining the connection details (user, server, driver, correct table name etc.),

I took these steps:

Checked the ODBC version here (Windows 10) ->
(search for) ODBC ->
Select 32/64 bit version ->
Drivers ->
Verify that the ODBC driver version is present there. If it is not, use this link to download the relevant driver: here

Reference Link: here

edited Oct 5, 2018 at 19:39

zx485

29.1k28 gold badges55 silver badges65 bronze badges

answered Oct 5, 2018 at 18:23

Anand Vamsee

2013 silver badges4 bronze badges

Comments

walkbob · Accepted Answer · 2016-12-14 08:21:17Z

0

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost:1433;DATABASE=Test')

This connection lack of instance name and the port shouldn't be writen like this.

my connection is this:

cn=pyodbc.connect('DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;PORT=1433;DATABASE=ybdb;UID=sa;PWD=*****')

enter image description here

edited Dec 14, 2016 at 8:21

answered Dec 14, 2016 at 8:11

walkbob

11 bronze badge

Collectives™ on Stack Overflow

pyodbc connection error when trying to connect to DB on localhost

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related