I'm trying to connect to a SAP SQL Anywhere database with pyodbc and the FreeTDS ODBC driver.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'mssql_database': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'blabla',
        'USER': 'blabla',
        'PASSWORD': 'blabla',
        'HOST': '10.65.1.20',
        'PORT': '1433',
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
        },
    },
    'sybase_database': {
        'ENGINE': 'django_pyodbc',
        'NAME': 'blabla',
        'USER': 'blabla',
        'PASSWORD': 'blabla',
        'HOST': '10.60.1.6',
        'PORT': '2638',
        'OPTIONS': {
            'driver': 'FreeTDS',
            'host_is_server': True,
        },
    },
}

Connection to mssql_database works. But connection to sybase_database ends with this error message: django.db.utils.DatabaseError: ('42000', "[42000] [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'SERVERPROPERTY' not found (504) (SQLExecDirectW)")

all traceback

$ python manage.py inspectdb --database=sybase_database
Traceback (most recent call last):
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django_pyodbc/base.py", line 491, in execute
    return self.cursor.execute(sql, params)
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'SERVERPROPERTY' not found (504) (SQLExecDirectW)")

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 338, in execute_from_command_line
    utility.execute()
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/__init__.py", line 330, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/base.py", line 390, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/base.py", line 441, in execute
    output = self.handle(*args, **options)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/commands/inspectdb.py", line 25, in handle
    for line in self.handle_inspection(options):
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/core/management/commands/inspectdb.py", line 38, in handle_inspection
    with connection.cursor() as cursor:
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/db/backends/base/base.py", line 162, in cursor
    cursor = self.make_debug_cursor(self._cursor())
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django_pyodbc/base.py", line 362, in _cursor
    if self.ops.sql_server_ver < 2005:
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django_pyodbc/operations.py", line 138, in _get_sql_server_ver
    cur.execute("SELECT CAST(SERVERPROPERTY('ProductVersion') as varchar)")
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 62, in execute
    return self.cursor.execute(sql)
  File "/home/pd/packaging/venv/lib/python3.6/site-packages/django_pyodbc/base.py", line 497, in execute
    raise utils.DatabaseError(*e.args)
django.db.utils.DatabaseError: ('42000', "[42000] [FreeTDS][SQL Server]SQL Anywhere Error -265: Procedure 'SERVERPROPERTY' not found (504) (SQLExecDirectW)")

$ pip list
Package       Version
------------- -------
Django        1.8
django-pyodbc 1.1.3
pip           21.3.1
pyodbc        4.0.32
setuptools    59.6.0
sqlany-django 1.13
sqlanydb      1.0.11
wheel         0.37.1

When I run this script

# hello_sybase.py
import pyodbc

try:
    con = pyodbc.connect('Driver={FreeTDS};'
            'Server=10.60.1.6,2638;'
                      'Database=blabla;'
                      'uid=blabla;pwd=blabla')
    cur = con.cursor()
    cur.execute("Select * from Test")
    for row in cur.fetchall():
        print (row)
    cur.close()
    con.close()
except Exception as e:
    print(str(e))

It works and prints all the rows. Is it at least possible to work with the results (rows) in a django template? It's sufficient to have read only database.

CodePudding user response：

According to the site for django-pyodbc, Sybase is not supported.

The traceback leads to this code, which seems to be SQL Server specific. You'd have to hack around to make that library compatible with your database... but since you say you don't necessarily need Django's ORM stuff, you can absolutely just connect to the secondary database using the raw pyodbc layer, e.g.

import pyodbc

# TODO: move this to `settings`?
CONN_STRING = 'Driver={FreeTDS};Server=10.60.1.6,2638;Database=blabla;uid=blabla;pwd=blabla'


def my_view(request):
    with pyodbc.connect(CONN_STRING) as conn:
        cur = conn.cursor()
        cur.execute('SELECT * FROM test')
        rows = list(cur.fetchall())
    return render(request, 'my_template.html', {'rows': rows})