Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
sqlalchemy
Home
+ new post
About this group
Join this group
SQLSoup and pymssql from Linux
Options
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
   21 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Ladislav Lenart  
View profile  
 More options Oct 5 2012, 9:40 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Fri, 05 Oct 2012 15:40:03 +0200
Local: Fri, Oct 5 2012 9:40 am
Subject: [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

I try to access a Microsoft SQL database from Linux (Debian testing):

    from sqlalchemy.ext.sqlsoup import SqlSoup

    conn_string = 'mssql+pymssql://user:pass@freetds_name'
    db = SqlSoup(conn_string)
    v = db.some_table.first()
    print v

freetds_name is the section name from /etc/freetds/freetds.conf

    [freetds_name]
    host = ...
    port = 1433
    tds version = 7.1
    asa database = DB

The above script fails in pymssql on line 83, because line 81 sets vers to None:

    def _get_server_version_info(self, connection):
        vers = connection.scalar("select @@version")
        m = re.match(
            r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
        if m:
            return tuple(int(x) for x in m.group(1, 2, 3, 4))
        else:
            return None

But the following works in tsql:

    1> select @@version
    2> go

    Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64)
                Apr 22 2011 19:23:43
                Copyright (c) Microsoft Corporation
                Workgroup Edition (64-bit) on Windows NT 6.1 <X64>
                (Build 7601: Service Pack 1) (Hypervisor)

    (1 row affected)

Any idea what is wrong?

Thank you,

Ladislav Lenart


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Discussion subject changed to "[Q] SQLSoup and pymssql from Linux" by Michael Bayer
Michael Bayer  
View profile  
 More options Oct 5 2012, 10:33 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Fri, 5 Oct 2012 10:33:45 -0400
Local: Fri, Oct 5 2012 10:33 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author

On Oct 5, 2012, at 9:40 AM, Ladislav Lenart wrote:

I dont have easy access to pymssql here so can you fully define what "fails" means ?  stack trace ?


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
lenart...@volny.cz  
View profile  
 More options Oct 5 2012, 1:40 pm
From: lenart...@volny.cz
Date: Fri, 05 Oct 2012 19:40:01 +0200 (CEST)
Local: Fri, Oct 5 2012 1:40 pm
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

> I dont have easy access to pymssql here so can you fully define what "fails" means ?  stack trace ?

I don't have access to my development environment during the weekend, so I cannot provide you with a stacktrace, but I try to better describe the issue:

    def _get_server_version_info(self, connection)
        vers = connection.scalar("select @@version")
        m = re.match(r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
        ...

The above code snippet is from the file pymssql (around line 80). The variable vers is set to None and because of that the following regex fails with error "Expected string or buffer". The None is returned by the call to scalar(). The code snippet (from memory, I don't remember its exact location and form):

    iter(resultproxy).next()

is called to get a next (first) result from the result proxy and it simply returns None as if there were no rows.

Ladislav Lenart

Od: "Michael Bayer" <mike...@zzzcomputing.com>


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 5 2012, 6:47 pm
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Fri, 5 Oct 2012 18:47:06 -0400
Local: Fri, Oct 5 2012 6:47 pm
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
what I can do for the moment is this patch, if you want to try it:

diff -r 17cab4ad55d5 lib/sqlalchemy/dialects/mssql/pymssql.py
--- a/lib/sqlalchemy/dialects/mssql/pymssql.py  Thu Oct 04 18:26:55 2012 -0400
+++ b/lib/sqlalchemy/dialects/mssql/pymssql.py  Fri Oct 05 18:46:01 2012 -0400
@@ -80,7 +80,7 @@
     def _get_server_version_info(self, connection):
         vers = connection.scalar("select @@version")
         m = re.match(
-            r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
+            r"\s*Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
         if m:
             return tuple(int(x) for x in m.group(1, 2, 3, 4))
         else:

otherwise, I'd consider using pyodbc for which the dialect and DBAPI are production quality.   I use pyodbc with FreeTDS on unix platforms in production.

On Oct 5, 2012, at 1:40 PM, lenart...@volny.cz wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 5:11 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 11:11:40 +0200
Local: Mon, Oct 8 2012 5:11 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

> otherwise, I'd consider using pyodbc for which the dialect and DBAPI are

production quality.   I use pyodbc with FreeTDS on unix platforms in production.

Ok, I can use pyodbc if it is the preferred choice. However I cannot make it
work either. I suspect that I supply bad connection string but am a little lost
in the docs...

The code:

from sqlalchemy.ext.sqlsoup import SqlSoup

if __name__ == '__main__':
    conn_string = 'mssql+pyodbc://username:pass\@w...@10.230.128.140:1433/ZFP_CRM'
    db = SqlSoup(conn_string)
    x = db.zfp_mlm_spol.first()

fails (see the traceback below). Note the password contains the character '@'.
The preceeding '\' is my attempt to escape it.

What connection string should I use to connect to MSSQL via pyodbc using freetds?

Thank you,

Ladislav Lenart

THE TRACEBACK:

Traceback (most recent call last):
  File
"/home/lada/.eclipse/org.eclipse.platform_3.8_155965261/plugins/org.python. pydev_2.6.0.2012062818/pysrc/pydevd.py",
line 1392, in <module>
    debugger.run(setup['file'], None, None)
  File
"/home/lada/.eclipse/org.eclipse.platform_3.8_155965261/plugins/org.python. pydev_2.6.0.2012062818/pysrc/pydevd.py",
line 1085, in run
    pydev_imports.execfile(file, globals, locals) #execute the script
  File "/home/lada/mine/devel/python/ZFP/zfp_connect.py", line 11, in <module>
    x = db.zfp_mlm_spol.first()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 807,
in __getattr__
    return self.entity(attr)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 804,
in entity
    return self.map_to(attr, tablename=attr, schema=schema)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 684,
in map_to
    schema=schema or self.schema)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 318, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 381, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 397, in
_autoload
    self, include_columns, exclude_columns
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2424,
in run_callable
    conn = self.contextual_connect()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2490,
in contextual_connect
    self.pool.connect(),
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 224, in connect
    return _ConnectionFairy(self).checkout()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 387, in __init__
    rec = self._connection_record = pool._do_get()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 741, in _do_get
    con = self._create_connection()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 188, in
_create_connection
    return _ConnectionRecord(self)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 270, in __init__
    self.connection = self.__connect()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 330, in __connect
    connection = self.__pool._creator()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line
80, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line
281, in connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver
Manager]Data source name not found, and no default driver specified (0)
(SQLDriverConnectW)') None None

On 6.10.2012 00:47, Michael Bayer wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 9:02 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 15:02:19 +0200
Local: Mon, Oct 8 2012 9:02 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello again.

It turned out that I was missing some ODBC-related packages and also needed to
configure freetds to work with unixodbc. I managed to finally do it, though it
was by no means easy for me (trial and error of several tutorials).

The following code works now:

import pyodbc
cnxn =
pyodbc.connect('DRIVER={FreeTDS};SERVER=1.2.3.4;PORT=1433;DATABASE=ZFP_CRM; UID=username;PWD=pass@cword;TDS_VERSION=8.0')
cursor = cnxn.cursor()
cursor.execute("select * from mlm_spol")
row = cursor.fetchone()
print row

However SqlSoup does not work. The code:

import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup
def connect():
    return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect)
db = SqlSoup(engine)
row = db.mlm_spol.first()
print row

fails with:

Traceback (most recent call last):
  File "/home/lada/mine/devel/python/ZFP/zfp_connect.py", line 16, in <module>
    x = db.mlm_spol.first()
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 807,
in __getattr__
    return self.entity(attr)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 804,
in entity
    return self.map_to(attr, tablename=attr, schema=schema)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 684,
in map_to
    schema=schema or self.schema)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 318, in __new__
    table._init(name, metadata, *args, **kw)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 381, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 397, in
_autoload
    self, include_columns, exclude_columns
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2426,
in run_callable
    return conn.run_callable(callable_, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1969,
in run_callable
    return callable_(self, *args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line
260, in reflecttable
    return insp.reflecttable(table, include_columns, exclude_columns)
  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line
412, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: mlm_spol

Any ideas?

Thanks,

Ladislav Lenart

On 8.10.2012 11:11, Ladislav Lenart wrote:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 9:22 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 15:22:08 +0200
Local: Mon, Oct 8 2012 9:22 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

UPDATE: The raw SqlSoup.execute() works:

import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

def connect():
    return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect)
db = SqlSoup(engine)
x = db.execute("select * from mlm_spol").fetchone()
print x

Any ideas what I have to do to make SqlSoup's ORM work? I am out of ideas.

Ladislav Lenart

On 8.10.2012 15:02, Ladislav Lenart wrote:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 8 2012, 10:21 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Mon, 8 Oct 2012 10:20:56 -0400
Local: Mon, Oct 8 2012 10:20 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
"no such table" usually means you're not connected to the database that you think you are.

I'd strongly suggest configuring an ODBC datasource within FreeTDS, and using standard connection techniques.   Hostname, port, tds version go into freetds.conf, and database names go into odbc.ini.

In freetds conf for example I have:

[ms_2005]
        host = 172.16.248.128
        port = 1213
        tds version = 8.0
        client charset = UTF8
        text size = 50000000

and on my mac in /Library/ODBC/odbc.ini I have:

[ODBC Data Sources]
ms_2005 = test

[ms_2005]
Driver      = /usr/local/lib/libtdsodbc.so
Description = test
Trace       = No
Servername  = ms_2005

I then connect with SQLAlchemy as:

create_engine("mssql://scott:tiger@ms_2005")

see http://freetds.schemamania.org/userguide/prepodbc.htm for freetds' docs on all this.

On Oct 8, 2012, at 9:02 AM, Ladislav Lenart wrote:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 11:10 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 17:10:29 +0200
Local: Mon, Oct 8 2012 11:10 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Ok, I will give it yet another try, but please note that the following works:

import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

def connect():
    return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect)
db = SqlSoup(engine)
x = db.execute("select * from mlm_spol").fetchone()
print x

i.e. raw SqlSoup.execute() sees the table and returns a valid result.

I regard this as a proof that the connection was established successfully and
the problem lies elsewhere. Am I wrong?

Ladislav Lenart

On 8.10.2012 16:20, Michael Bayer wrote:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 8 2012, 11:19 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Mon, 8 Oct 2012 11:18:56 -0400
Local: Mon, Oct 8 2012 11:18 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author

On Oct 8, 2012, at 11:10 AM, Ladislav Lenart wrote:

perhaps.  Maybe the connection doesn't have correct access to the information schema tables, as SQLSoup relies upon table reflection.  you'd need to run with echo='debug' on your engine to see exactly what queries are being emitted and the rows being returned, and determine why an information schema row for "mlm_spol" isn't being returned.

 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 11:43 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 17:43:00 +0200
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

> perhaps.  Maybe the connection doesn't have correct access to the information

schema tables, as SQLSoup relies upon table reflection.  you'd need to run with
echo='debug' on your engine to see exactly what queries are being emitted and
the rows being returned, and determine why an information schema row for
"mlm_spol" isn't being returned.

I did it and it seems that there is a str/unicode problem when dealing with the
information shema (see below, it's rather long).

I tried it with charset=UTF8 to pyodbc.connect string. I also tried various
combinations of create_engine options supports_unicode_binds and convert_unicode
but none of them worked.

Ladislav Lenart

CODE:

import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

def connect():
   return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect, echo='debug')
db = SqlSoup(engine)
x = db.execute("select * from mlm_spol").fetchone()
print x

ITS DEBUG OUTPUT:

pydev debugger: starting
2012-10-08 17:19:53,239 INFO sqlalchemy.engine.base.Engine SELECT user_name() as
user_name;
2012-10-08 17:19:53,240 INFO sqlalchemy.engine.base.Engine ()
2012-10-08 17:19:53,246 DEBUG sqlalchemy.engine.base.Engine Col ('user_name',)
2012-10-08 17:19:53,247 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U00660065\U00610072\U00740063\U006c0061', )
2012-10-08 17:19:53,253 INFO sqlalchemy.engine.base.Engine
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2012-10-08 17:19:53,253 INFO sqlalchemy.engine.base.Engine
(u'\U00660065\U00610072\U00740063\U006c0061',)
2012-10-08 17:19:53,259 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2012-10-08 17:19:53,286 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-10-08 17:19:53,288 INFO sqlalchemy.engine.base.Engine select * from mlm_spol
2012-10-08 17:19:53,289 INFO sqlalchemy.engine.base.Engine ()
2012-10-08 17:19:53,304 DEBUG sqlalchemy.engine.base.Engine Col ('id_mlm_spol',
'id_mlm_spol_master', 'id_mlm_spol_ridici', 'id_mlm_spol_reditel',
'id_mlm_spol_predstav', 'id_mlm_spol_ppz', 'id_mlm_spol_psedm',
'id_mlm_spol_gar_cz', 'id_mlm_spol_gar_sk', 'id_mlm_spol_reditel_cz',
'id_mlm_spol_reditel_sk', 'id_mlm_spol_predstav_cz', 'id_mlm_spol_predstav_sk',
'id_mlm_spol_ridici_cz', 'id_mlm_spol_ridici_sk', 'id_mlm_spol_psedm_cz',
'id_mlm_spol_psedm_sk', 'id_mlm_firma_subjekt', 'pocet_primi', 'pocet_celkem',
'typ_spol', 'info', 'info_extended', 'os_cislo', 'os_cislo_sk', 'alt_cislo_1',
'alt_cislo_2', 'alt_cislo_3', 'pozice', 'dosahnuti_pozice', 'stav',
'dosahnuti_stavu', 'datum_dosahnuti_stavu', 'smlouva', 'prvni_aplikace_smlouvy',
'vznik_vykonu', 'rfp', 'body_vlastni', 'body_vlastni_alt',
'body_vlastni_alt_trans', 'body_skup', 'body_skup_alt', 'body_skup_alt_trans',
'body_top_struktury', 'kod_banky', 'cislo_uctu', 'spec_symbol', 'eu_banka',
'rod_cis', 'ico', 'pozice_puv', 'postup', 'os_cislo_reditel', 'os_cislo_ridici',
'os_cislo_sponzor', 'os_cislo_predstav', 'typoval', 'zmena', 'poznamka',
'pokus', 'os_cislo_sponzor_new', 'body_celkem', 'blokovany_postup',
'evidencni_cislo', 'body_celkem_200501', 'cislo_slev_karty', 'reg_cislo_mf',
'reg_cislo_uft', 'datum_prid_slev_karty', 'obdobi_prid_slev_karty',
'id_mlm_spol_gar_orig_cz', 'id_mlm_spol_gar_orig_sk', 'zmena_gar_cz',
'zmena_gar_sk', 'duvod_zmeny_gar_cz', 'duvod_zmeny_gar_sk', 'datum_last_cz',
'datum_last_sk', 'id_mlm_smlouva_last_cz', 'id_mlm_smlouva_last_sk',
'id_mlm_strukt_man_cz', 'id_mlm_strukt_man_sk', 'id_mlm_spol_ros_cz',
'id_mlm_spol_ros_sk', 'tisk_ps_1', 'tisk_ps_2', 'tisk_ps_3', 'tisk_ps_4',
'tisk_ps_1_rucni', 'tisk_ps_3_rucni', 'tisk_ps_5', 'tisk_ps_5_rucni',
'id_mlm_spol_psedm_akt_cz', 'id_mlm_spol_psedm_akt_sk', 'id_mlm_spol_hrk_cz',
'id_mlm_spol_hrk_sk', 'provizni_stav', 'menit_automaticky_pstav',
'zda_uplatnovat_kredit', 'perioda_dosahnuti_pozice', 'firma')
2012-10-08 17:19:53,306 DEBUG sqlalchemy.engine.base.Engine Row (277581, 268893,
37494, 37494, 20198, None, 37494, 119835, 18954, 37494, 18954, 20198, 18954,
37494, 18996, 37494, 18996, None, 0, 0, 'O', None, None, '414712', None, None,
None, None, '1', None, 1, '201208', datetime.datetime(2012, 8, 25, 0, 0), None,
None, None, None, Decimal('0.000000'), Decimal('0.000000'), Decimal('0.000000'),
Decimal('0.000000'), Decimal('0.000000'), Decimal('0.000000'),
Decimal('0.000000'), None, None, None, None, None, None, None, None, None, None,
None, None, None, None, None, None, None, Decimal('0.000000'), None, None, None,
None, None, None, None, None, None, None, None, None, None, None, None, None,
None, None, 37494, 18954, 18996, 19009, 'A', None, None, None, 'N', None, None,
None, 37494, 20198, 20198, 18954, False, True, True, 0, None)

CODE:

import pyodbc
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

def connect():
   return
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0;')
engine = create_engine('mssql+pyodbc://', creator=connect, echo='debug')
db = SqlSoup(engine)
x = db.mlm_spol.fetchone()
print x

ITS DEBUG OUTPUT:

pydev debugger: starting
2012-10-08 17:33:07,175 INFO sqlalchemy.engine.base.Engine SELECT user_name() as
user_name;
2012-10-08 17:33:07,176 INFO sqlalchemy.engine.base.Engine ()
2012-10-08 17:33:07,181 DEBUG sqlalchemy.engine.base.Engine Col ('user_name',)
2012-10-08 17:33:07,182 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U00660065\U00610072\U00740063\U006c0061', )
2012-10-08 17:33:07,188 INFO sqlalchemy.engine.base.Engine
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2012-10-08 17:33:07,189 INFO sqlalchemy.engine.base.Engine
(u'\U00660065\U00610072\U00740063\U006c0061',)
2012-10-08 17:33:07,194 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2012-10-08 17:33:07,243 INFO sqlalchemy.engine.base.Engine SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY
[COLUMNS_1].[ORDINAL_POSITION]
2012-10-08 17:33:07,244 INFO sqlalchemy.engine.base.Engine (u'mlm_spol', u'dbo')
2012-10-08 17:33:07,259 DEBUG sqlalchemy.engine.base.Engine Col ('TABLE_SCHEMA',
'TABLE_NAME', 'COLUMN_NAME', 'IS_NULLABLE', 'DATA_TYPE', 'ORDINAL_POSITION',
'CHARACTER_MAXIMUM_LENGTH', 'NUMERIC_PRECISION', 'NUMERIC_SCALE',
'COLUMN_DEFAULT', 'COLLATION_NAME')
2012-10-08 17:33:07,264 INFO sqlalchemy.engine.base.Engine sp_columns
@table_name = 'mlm_spol', @table_owner = 'dbo'
2012-10-08 17:33:07,265 INFO sqlalchemy.engine.base.Engine ()
2012-10-08 17:33:07,282 DEBUG sqlalchemy.engine.base.Engine Col
('TABLE_QUALIFIER', 'TABLE_OWNER', 'TABLE_NAME', 'COLUMN_NAME', 'DATA_TYPE',
'TYPE_NAME', 'PRECISION', 'LENGTH', 'SCALE', 'RADIX', 'NULLABLE', 'REMARKS',
'COLUMN_DEF', 'SQL_DATA_TYPE', 'SQL_DATETIME_SUB', 'CHAR_OCTET_LENGTH',
'ORDINAL_POSITION', 'IS_NULLABLE', 'SS_DATA_TYPE')
2012-10-08 17:33:07,283 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070', 4,
u'\U006e0069\U00200074\U00640069\U006e0065\U00690074\U00790074', 10, 4, 0, 10,
0, None, None, 4, None, None, 1, 'NO', 56)
2012-10-08 17:33:07,285 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U0061006d\U0 0740073\U00720065',
4, u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 2, 'YES', 38)
2012-10-08 17:33:07,286 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U00690072\U0 0690064\U00690063',
4, u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 3, 'YES', 38)
2012-10-08 17:33:07,287 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U00650072\U0 0690064\U00650074',
4, u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 4, 'YES', 38)
2012-10-08 17:33:07,288 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U00720070\U0 0640065\U00740073\U00760061',
4, u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 5, 'YES', 38)
2012-10-08 17:33:07,289 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U00700070', 4,
u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 6, 'YES', 38)
2012-10-08 17:33:07,290 DEBUG sqlalchemy.engine.base.Engine Row
(u'\U0046005a\U005f0050\U00520043', u'\U00620064',
u'\U006c006d\U005f006d\U00700073\U006c006f',
u'\U00640069\U006d005f\U006d006c\U0073005f\U006f0070\U005f006c\U00730070\U0 0640065',
4, u'\U006e0069', 10, 4, 0, 10, 1, None, None, 4, None, None, 7, 'YES', 38)
2012-10-08 ...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 8 2012, 11:45 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Mon, 8 Oct 2012 11:45:12 -0400
Local: Mon, Oct 8 2012 11:45 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
your freetds datasource should be configured with CLIENT_CHARSET=utf8 as I illustrated earlier:

[ms_2005]
        host = 172.16.248.128
       port = 1213
       tds version = 8.0
       client charset = UTF8
       text size = 50000000

On Oct 8, 2012, at 11:43 AM, Ladislav Lenart wrote:

...

read more »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 8 2012, 12:10 pm
From: Ladislav Lenart <lenart...@volny.cz>
Date: Mon, 08 Oct 2012 18:10:07 +0200
Local: Mon, Oct 8 2012 12:10 pm
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

I adjusted the ODBC/FreeTDS condifugration according to your suggestions but
still get the "NoSuchTableError: mlm_spol".

freetds.conf:

[zfp]
host = 10.230.128.140
port = 1433
tds version = 8.0
asa database = ZFP_CRM
client charset = utf8
text size = 50000000

odbc.ini:

[ODBC Data Sources]
zfp = test

[zfp]
Driver = /usr/lib/libtdsodbc.so
Description = test
Trace = No
Servername = zfp

odbcinst.ini (for the sake of completeness):

[FreeTDS]
Driver = /usr/lib/libtdsodbc.so
UsageCount = 2

The code:

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

if __name__ == '__main__':
    engine = create_engine("mssql://efractal:efR@cZFP13@zfp", echo='debug')
    db = SqlSoup(engine)
    x = db.mlm_spol.fetchone()
    print x

still fails with NoSuchTableError: mlm_spol

Its debug output is the same as in my previous email which used different
connect style.

And the code that uses db.execute('select * from mlm_spol').fetchone() still
works...

Any other ideas?

Ladislav Lenart

On 8.10.2012 17:45, Michael Bayer wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 8 2012, 1:06 pm
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Mon, 8 Oct 2012 13:06:37 -0400
Local: Mon, Oct 8 2012 1:06 pm
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
if you didnt have this problem with pymssql then please apply the patch I sent previously.

However, I'm going to bet the problem remains as it seems something is not right with how your database and/or client is configured.  I'm not familiar with the encoding pattern seen in your information_schema queries.  you may have to reproduce the issue using plain SELECT statements against information_schema and then seek outside help.

diff -r 17cab4ad55d5 lib/sqlalchemy/dialects/mssql/pymssql.py
--- a/lib/sqlalchemy/dialects/mssql/pymssql.py  Thu Oct 04 18:26:55 2012 -0400
+++ b/lib/sqlalchemy/dialects/mssql/pymssql.py  Fri Oct 05 18:46:01 2012 -0400
@@ -80,7 +80,7 @@
    def _get_server_version_info(self, connection):
        vers = connection.scalar("select @@version")
        m = re.match(
-            r"Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
+            r"\s*Microsoft SQL Server.*? - (\d+).(\d+).(\d+).(\d+)", vers)
        if m:
            return tuple(int(x) for x in m.group(1, 2, 3, 4))
        else:

On Oct 8, 2012, at 12:10 PM, Ladislav Lenart wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 9 2012, 6:03 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Tue, 09 Oct 2012 12:03:13 +0200
Local: Tues, Oct 9 2012 6:03 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

No, I was not able to connect via pymssql. Furthemore I would like to use pyodbc
if it is the preferred way. I just did not know that when I started with pymssql.

It really is a misconfigured character encoding issue in pyodbc / freetds on my
part. I am just clueless as to what should I set to what to make it all work.

The code:

    cnxn =
pyodbc.connect('DRIVER={FreeTDS};SERVER=10.230.128.140;PORT=1433;DATABASE=Z FP_CRM;UID=efractal;PWD=efR@cZFP13;TDS_VERSION=8.0')
    cursor = cnxn.cursor()
    cursor.execute("select prijmeni from osoba where id_osoba = 462493")
    row = cursor.fetchone()
    print row

prints:

    ('Ne?asov', )

The correct value is 'Nečasová'. Ideally I would like to see the following:

    (u'Nečasová', )

On a side note, the returned value is one character shorter. I would expected:

    ('Ne?asov?', )

However, when I connect via tsql, it works correcly:

    tsql -S zfp -U efractal
    Password:
    locale is "cs_CZ.UTF-8"
    locale charset is "UTF-8"
    using default charset "utf8"
    1> select prijmeni from osoba where id_osoba = 462493
    2> go
    prijmeni
    Nečasová
    (1 row affected)
    1>

I guess I am on my own now. Anyway, thank you for your kind assistance.

Ladislav Lenart

On 8.10.2012 19:06, Michael Bayer wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 9 2012, 6:40 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Tue, 09 Oct 2012 12:39:55 +0200
Local: Tues, Oct 9 2012 6:39 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Also, this works correctly:

isql -v zfp efractal efR@cZFP13
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select prijmeni from osoba where id_osoba = 462493
prijmeni

              Nečasová

                          SQLRowCount returns 1
1 rows fetched
SQL>

This does not:

iusql -v zfp efractal efR@cZFP13
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select prijmeni from osoba where id_osoba = 462493
prijmeni

              asov

                         SQLRowCount returns 1
1 rows fetched
SQL>

Ladislav Lenart

On 9.10.2012 12:03, Ladislav Lenart wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 9 2012, 10:19 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Tue, 9 Oct 2012 10:19:33 -0400
Local: Tues, Oct 9 2012 10:19 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author

On Oct 9, 2012, at 6:03 AM, Ladislav Lenart wrote:

you definitely, definitely need "client charset" to be part of your FreeTDS config, either in that URL string up there (not sure if that's supported), or preferably in your freetds.conf.   pyodbc is pretty sensitive to this.

> However, when I connect via tsql, it works correcly:

yeah, the whole FreeTDS story is awful, I don't understand any of it either.     I'm still at "wave a dead chicken" stage with  FreeTDS (http://dictionary.reference.com/browse/wave+a+dead+chicken).


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Ladislav Lenart  
View profile  
 More options Oct 9 2012, 11:07 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Tue, 09 Oct 2012 17:07:28 +0200
Local: Tues, Oct 9 2012 11:07 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello.

I made some progress. I have client charset in my freetds config file. I am also
certain that the config and the charset is used by pyodbc / freetds combo.
Without it I get 'Ne?...'. With it I get back a str encoded in utf-8. I also
enabled freetds logging where I clearly see two conversions being prepared:

iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion

If I comment the client charset line in the config, I see:

iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion

I beleive the second one has to do with description_encoding which is 'latin-1'
by default (in MSDialect_pyodbc.__init__). However I tried to set it to utf-8,
utf-16le and whatnot without any effect (in debugger).

I also found out that my problem is not in information_schema per se but with
all nvarchar values / columns. For example this

    u'\U0073006f\U0062006f'

is actually a garbled string 'osob':

    u'\U0073006f\U0062006f'
    u'\U006f0073\U006f0062'
    u'\u006f\u0073\u006f\u0062' = u'osob'

though I have no idea how and where it comes into existence. Furthermore the
correct value is 'osoba' (a table name). I guess the conversion stripped out the
odd character.

Current state of nvarchar processing in my dev env:
tsql OK
isql OK
pyodbc KO

Ladislav Lenart

On 9.10.2012 16:19, Michael Bayer wrote:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Discussion subject changed to "SOLVED [Q] SQLSoup and PYODBC from Linux" by Ladislav Lenart
Ladislav Lenart  
View profile  
 More options Oct 10 2012, 11:24 am
From: Ladislav Lenart <lenart...@volny.cz>
Date: Wed, 10 Oct 2012 17:23:30 +0200
Local: Wed, Oct 10 2012 11:23 am
Subject: Re: [sqlalchemy] SOLVED [Q] SQLSoup and PYODBC from Linux
Print | Individual message | Show original | Report this message | Find messages by this author
Hello!

I finally managed to solve the problem (with the great help of my colleagues)!
The culprit: python-pyodbc package in Debian/testing is nearly THREE years old
now (version 2.1.7). I removed it and easy-installed the version 3.0.6 which
does not have the encoding bug anymore. Now everything works like a charm,
inluding SqlSoup. I even receive unicode objects directly for both VARCHAR and
NVARCHAR columns!

For future reference: If you want to connect to MSSQL server from a Linux
(Debian) machine, you need to...

[1] Install unixodbc:

aptitude install unixodbc

I also have unixodbc-dev installed, but I am not sure if it is necessary.

[2] Install freetds and the driver:

aptitude install freetds-bin tdsodbc

[3] Edit /etc/odbcinst.ini (note: DO NOT indent lines in the odbc config files!):

[FreeTDS]
# See dpkg -L tdsodbc | grep libtdsodbc
Driver = /path/to/libtdsodbc.so
UsageCount = 1

[4] Edit /etc/odbc.ini:

[ODBC Data Sources]
zfp = test

[zfp]
Driver = FreeTDS # The section name from /etd/odbcinst.ini
Description = test
Servername = zfp # The section name from /etc/freetds/freetds.conf (see below)
# Do NOT use option 'Server'.
TDS_Version = 8.0       # Important

[5] Edit /etc/freetds/freetds.conf:

[zfp]
host = 10.230.128.140
port = 1433
asa database = ZFP_CRM  # Database name
tds version = 8.0       # Important
client charset = UTF-8  # Important
text size = 50000000

[6] Install python2.7-dev which is needed for the next step, because pyodbc
compiles some stuff and thus needs header files:

aptitude install python2.7-dev

[7] Install pyodbc "manually" (i.e. without aptitude):

easyinstall pyodbc

[8] Verify that freetds works (zfp is the section name in
/etc/freetds/freetds.conf):

tsql -S zfp -U username

Try to select some strings to see that they display correctly.

[9] Verify that unixodbc works (zfp here is the section name in /etc/odbc.ini):

isql zfp username password

Again, try to select some strings to see that they display correctly.

[10] Verify that pyodbc works:

import pyodbc
cnxn = pyodbc.connect("DSN=zfp;UID=username;PWD=password", unicode_results=True)
cursor = cnxn.cursor()
# Select some VARCHAR column.
(x,) = cursor.execute("select...").fetchone()
# Should print a meaningful unicode representation of the result string.
print repr(x)
# Repeat the same for NVARCHAR column. The result should be the same, i.e.
# proper unicode representation).
(x,) = cursor.execute("select...").fetchone()
print repr(x)

[11] Connect via SQLAlchemy:

from sqlalchemy.engine import create_engine
from sqlalchemy.ext.sqlsoup import SqlSoup

# zfp here is the section name in /etc/odbc.ini
engine = create_engine(
    "mssql+pyodbc://username:password@zfp",
    convert_unicode=True,
    echo='debug'
)
db = SqlSoup(engine)
# Same as in step 10
db.execute(...)
# OR
x = db.some_table.filter(...).one()

Enjoy!

Ladislav Lenart


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Discussion subject changed to "[Q] SQLSoup and pymssql from Linux" by Yap Sok Ann
Yap Sok Ann  
View profile  
 More options Oct 13 2012, 12:20 am
From: Yap Sok Ann <sok...@gmail.com>
Date: Fri, 12 Oct 2012 21:20:39 -0700 (PDT)
Local: Sat, Oct 13 2012 12:20 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author

On Tuesday, October 9, 2012 10:19:34 PM UTC+8, Michael Bayer wrote:

> yeah, the whole FreeTDS story is awful, I don't understand any of it
> either.     I'm still at "wave a dead chicken" stage with  FreeTDS (
> http://dictionary.reference.com/browse/wave+a+dead+chicken).

The ODBC Driver 1.0 for Linux from Microsoft
(http://www.microsoft.com/en-us/download/details.aspx?id=28160) works very
well for me. It allows me to use the same driver (SQL Server Native Client
11.0) on both Linux-based development machine and Windows-based production
server.

With this added to odbcinst.ini:

[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1

I can then use a simple SQLAlchemy URL:

mssql+pyodbc://<username>:<password>@<server>/<database>?driver=SQL+Server+ Native+Client+11.0


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Michael Bayer  
View profile  
 More options Oct 15 2012, 1:24 am
From: Michael Bayer <mike...@zzzcomputing.com>
Date: Mon, 15 Oct 2012 01:24:12 -0400
Local: Mon, Oct 15 2012 1:24 am
Subject: Re: [sqlalchemy] [Q] SQLSoup and pymssql from Linux
Print | Individual message | Show original | Report this message | Find messages by this author

On Oct 13, 2012, at 12:20 AM, Yap Sok Ann wrote:

OK this is really fascinating, as my sysadmin scared me away from MS's product, but if you're saying its working, then that is really amazing.  Of course the proof would be that I can run unicode through it in any way I see fit and it wouldn't choke.  Will try to look into this, as never using FreeTDS again would be a good thing.

 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »

Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google