Forums

Hello,

I am trying to use pyodbc with sql server. However I am getting the following error: pyodbc.Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Here is my connection string: self.conn = pyodbc.connect('DRIVER={SQL Server};SERVER=<servername>;UID=<userid>;PWD=<pwd>;DATABASE=<database>')

Any ideas? Works on my local machine.

From a free account, you can only access external resources that are on our whitelist, and only via HTTP / HTTPS. Paid accounts can access any site using any protocol.

I am having similar issue, and I have a paid account. I am able to connect on my local machine. Any ideas?

Error:

DB CONNECT ATTEMPT('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

What is the datasource that you are trying to connect to? (just want to double check that you are not trying to connect to a local source)

Is your local machine running Windows? I get this error too when I try to connect to Microsoft Azure in PythonAnywhere. There are python modules you need to use on Linux/OS X machines, but they are pretty outdated. I haven't been able to connect except on Windows machines.

Interesting, thanks for letting us know! We've got the most up-to-date ODBC libraries for our version of Linux (Ubuntu 14.04 Trusty) and I know some people are using them without problems, but it's entirely possible that they just happened to be connecting to databases that just happened to be using older MS SQL servers that were compatible with our libraries.

This is not my local machine, it is a remote Windows Server 2008 R2 running a SQL Server database. I have just made a simple script just to try and make a connection, but I still get the same error:

Not a python master by any means, not sure what is wrong here.

import pyodbc

host = "XX.XX.XXX.XX"
database = "TestDB"
username = "dtinnxx"
password = "password"

print ("DB CONNECT ATTEMPT")
try:
    cs = 'DSN=%s;UID=%s;PWD=%s;DATABASE=%s;' % (host, username, password, database)
    cnxn = pyodbc.connect(cs)
    print ("SUCCESS")
except Exception as e:
    print ("Error: " + str(e))

Always errors out:

Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

[edit by admin: formatting]

I think the problem is that you're giving the host IP as the Data Source Name, so it's trying to find a DSN with a name that matches that IP address. A DSN is a pre-defined hostname and driver (and potentially other things) that's defined in a configuration file like ~/.odbc.ini.

If that's correct, the connection string should be

'DRIVER={SQL Server};SERVER=<servername>;UID=<userid>;PWD=<pwd>;DATABASE=<database>'

...where <servername> is the IP address you have in your host variable, and <userid>, <pwd> and <database> are as you already have them.

Give that a go and let us know if it works.

I am having the same issue. I have a paid account and using Python 3.5 - had to pip install pyodbc for it. I can import pyodbc but even using your format above (and even on a whitelisted site) I still get the same error as above.

import pypyodbc
cnxnMS = pyodbc.connect("""DRIVER={SQL Server};SERVER='www.xmlsoccer.com';UID='me';PWD='pass';DATABASE='me'""")

Hi,

I actually had to use pymssql to get it to work. (per PA support). Not sure why pyodbc does not work. Try the following:

From a bash console, run the following two commands (adjusting the Python version from 3.5 if appropriate):

export PYMSSQL_BUILD_WITH_BUNDLED_FREETDS=1
pip3.5 install --user pymssql

Then in your code:

import pymssql

host = <your servier ip address>
username = <your db user name>
password = <your db password>
database = <your database name>
...
 conn = pymssql.connect(host, username, password, database)
 cursor = conn.cursor()

[edit by admin: formatting]

Thanks for posting that! We should have updated this forum thread after talking to you (I think it was over email?).

You're right, pymssql is the only option that works right now -- we think the problem with pyodbc is caused by a missing system library (an operating-system-level package that can't be installed using pip), and we'll get that installed for the next system update. But in the meantime, your suggestion is definitely the best one.

Hi everyone, we pushed out our new system changes today and installed the missing system library. So pyodbc should be working now!

An update to that -- it does work, but there's some slightly fiddly setup that you need to do. We've put a full guide to connecting to Microsoft SQL Server, which includes pyodbc, on the help pages.

UPDATE: The Driver is now found and working.

I followed this guide using pyodbc, using pymssql is not an option anymore since it's not supported anymore. I seem to be having problems locating the driver [TDS driver].

Getting this error message:

"pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')"

Also when i call the pyodbc.drivers() function it returns empty.

Are you using the same data source name in your connection string as you created in odbc.ini?

The odbc.ini is declared like this:

[sqlserverdatasource]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver

and the connection string is declared like this with my own 'database', 'user' and 'pw' :

conn = pyodbc.connect('DSN=sqlserverdatasource;Database=<database>;Uid=<user>@sf.database.windows.net;Pwd=<PW>;Encrypt=yes;Connection Timeout=30;')

But its working now, so i'm happy :)

[edit by admin: formatting]

Excellent, glad you got it working! Thanks for the heads-up about pymssql, we didn't know about that. Here's a link to the notice that the project is being discontinued if anyone else is interested in the details.

We'll update the help page so that we no longer recommend it. [edit: that's done now]

Hello, I am using django3.0.6 and I have the same error, how to solve it in my case? I connect to the mssql database in the settings.py file.

https://i.ibb.co/vsxBDpm/2.jpg

https://i.ibb.co/3yWdqzL/image.jpg

Unfortunately with a free account you cannot connect to external databases, so while the specific error you're getting is happening before you run into that restriction, even if that error was fixed you would need a paid account in order to connect to that database.

I am ready to use a paid account, does this solve the problem?

Well, I'm using a paid account, but it didn't help. Can anyone solve this problem?

The first step in connecting to MS SQL server would be to try following the instructions on our help page so that you can at least make sure that the database will let you access it from our servers. Could you try that? Once that's working, we can move on to getting it working with Django.

I tried it and it worked. How do I explain this to Django?

That's good news. In that case, you should be able to use the settings that you had earlier, but I think you need to remove the "OPTIONS" section and just rely on the default there. One other question, in case there are still problems -- is your MS SQL server on Azure? If so, you'll need to specify yourusername@yourservername as the user name, not just yourusername.

My MSSQL database is not in Azure, it is on a VPS server. I removed the "Options" section and again I have a similar error.

https://ibb.co/7tMjJFg

https://ibb.co/TP8b892

Could you post the full stack trace? You can just copy/paste it into the forums here, then select it and click the code button at the top of the input field (the one that looks like two lines of 1s and 0s). There's no need to post an image.

.

Traceback (most recent call last):
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
    self.connect()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 197, in connect
    self.connection = self.get_new_connection(conn_params)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/sql_server/pyodbc/base.py", line 314, in get_new_connection
    timeout=timeout)
    pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")
    The above exception was the direct cause of the following exception:
    Traceback (most recent call last):
    File "manage.py", line 22, in <module>
    main()
    File "manage.py", line 18, in main
    execute_from_command_line(sys.argv)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/core/management/commands/migrate.py", line 86, in handle
    executor = MigrationExecutor(connection, self.migration_progress_callback)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/migrations/executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/migrations/loader.py", line 49, in __init__
    self.build_graph()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/migrations/loader.py", line 212, in build_graph
    self.applied_migrations = recorder.applied_migrations()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 76, in applied_migrations
    if self.has_table():
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/migrations/recorder.py", line 56, in has_table
    return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 260, in cursor
    return self._cursor()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/sql_server/pyodbc/base.py", line 218, in _cursor
    conn = super()._cursor()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 236, in _cursor
    self.ensure_connection()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
    self.connect()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 220, in ensure_connection
    self.connect()
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/django/db/backends/base/base.py", line 197, in connect
    self.connection = self.get_new_connection(conn_params)
    File "/home/nipponApi/.virtualenvs/nipponapi.pythonanywhere.com/lib/python3.6/site-packages/sql_server/pyodbc/base.py", line 314, in get_new_connection
    timeout=timeout)
    django.db.utils.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

[edit by admin: formatting]

Thanks! I think I see the fix you need now. You were right in your original settings to specify the OPTIONS section with the driver key -- it just needed to match the one from the help page. Instead of "ODBC Driver 11 for SQL Server", I think it should be "FreeTDS".

django.db.utils.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found (0) (SQLDriverConnect)")

I have a similar error in this case.

That's even more perplexing. Perhaps we can get it working using the DSN that we know worked, because you were able to get it working when you went through that help page earlier. Try adding this to your OPTIONS setting, at the same level as the driver:

"dsn": "sqlserverdatasource"

(Replace sqlserverdatasource with the data source name you set up in odbc.ini if you used something different.)

django.db.utils.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

Now, I have a different error.

What are the contents of the file odbc.ini in your home directory?

[sqlserverdatasource] Driver = FreeTDS Description = ODBC connection via FreeTDS Trace = No Servername = sqlserver

The FreeTDS documentation has a page about troubleshooting work through that and let us know the results and we'll see what we can discover.

I tried many ways. The configuration files were created correctly. I have sent tsql requests from the console and everything works. But django doesn't want to work anyway with my database. I found this article for Django> = 3 how to connect to MSSQL db with Django>=3 on Unix systems . But this did not help. The pythonanywhere support also cannot help anything, they cannot give a ready-made option for connecting and no one can figure out and configure it. The conclusion is that MICROSOFT SQL CANNOT BE FRIENDS WITH DJANGO ON PYTHONANYWHERE. Thank you all) I will use MySQL.

That really does seem to be the best option; sorry we couldn't help more. No-one else that we're aware of uses MS SQL server with Django on our platform -- everyone that we're aware of who is linking to SQL server from PythonAnywhere is using it in their scripts using pyodbc.connect directly. And the ticket on the Azure site that you link to suggests that you're not the only person who's been getting frustrated trying to hook it up to Django, even on other platforms.