2

I'm new to Python + Flask + Flask Appbuilder but I am a professional Java developer. I've been working on a small app that I initially used SqlLite and now I want to move into SQL Server, which will be the production database. I can't seem to get the connection right. I have tried using a DSN but I get an error message indicating there is a mismatch between the driver and something else (Python?). The searches on this error seem to indicate the driver is 32 bit and Python is 64. Still I can't get that working so I thought I'd try to connect directly. I'd prefer not using a DSN anyway. I've searched the web and can't find an example that works for me.

I have imported pyodbc. This is the current way I'm trying to connect:

params = urllib.quote_plus("DRIVER={SQL Server};SERVER=devsql07:1433;DATABASE=DevOpsSnippets;UID=<user>;PWD=<password>")
SQLALCHEMY_DATABASE_URI = "mssql+pyodbc:///?odbc_connect=%s" % params

This produces the following error message:

2016-02-17 07:11:38,115:ERROR:flask_appbuilder.security.sqla.manager:DB Creation and initialization failed: (pyodbc.Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConnectParams()). (14)')

Can anyone help me get this connection correct?

I really appreciate any help.

AustinJim
  • 41
  • 1
  • 5

2 Answers2

2

if you are using pyodbc you should be able to connect this way

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourServer;DATABASE=yourDatabase;UID=;PWD=')

#putting to use
SQL = "select Field1, Field2 from someTable"
cursor = cnxn.cursor()
cursor.execute(SQL)
row = cursor.fetchall()
for r in row:
    print r[0] #field1
    print r[1] #field2
yoyoyoyo123
  • 2,362
  • 2
  • 22
  • 36
  • Thanks for the help. My problem was that the DBA had not set this SQL Server instance on port 1433. I removed the port number and connected with no problem. Your code did verify that I had the driver correct and that the problem must be in the connection string itself. Thanks for the help! – AustinJim Feb 18 '16 at 13:32
  • darn DBA's. glad your problem is gone. – yoyoyoyo123 Feb 18 '16 at 15:28
0

The port should be specified through a comma. Specify the connection string as

DRIVER={SQL Server};SERVER=devsql07,1433;DATABASE.....
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Andrey
  • 1