Database connectivity

Discussion in 'Python' started by SectorUnknown, Nov 24, 2003.

  1. I've written a database (Access mdb) front-end using Python/wxpython/and
    ADO. However, the scope of the project has changed and I need to access
    the same data on an MSSQL server. Also, the front-end needs to be cross-
    platform (Windows and Linux).

    Does anyone have any suggestions on what database connectivity I should
    use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
    the best way to go.

    BTW, although I would love to move away from the MSSQL server, that's
    not going to change.
    SectorUnknown, Nov 24, 2003
    #1
    1. Advertising

  2. SectorUnknown wrote:
    > I've written a database (Access mdb) front-end using Python/wxpython/and
    > ADO. However, the scope of the project has changed and I need to access
    > the same data on an MSSQL server. Also, the front-end needs to be cross-
    > platform (Windows and Linux).
    >
    > Does anyone have any suggestions on what database connectivity I should
    > use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
    > the best way to go. [...]


    AFAIC you have two options:

    - Use mxODBC and save yourself/your company/your employer a considerable
    amount of time.

    - Program to the Python DB-API v2.0 and work around the differences
    between the various DB-API modules you'll need yourself. You'll probably
    end up the n-th abstraction layer on top of the DB-API.

    Another possibility is to see if an OR-thingie like PDO/SQLObject/...
    actually helps for database abstraction. I suppose they'll get really
    "fun" to use once you need advanced queries, though. Does anybody have
    any real-life experience with any of these Python OR mappers?

    -- Gerhard
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Nov 24, 2003
    #2
    1. Advertising

  3. SectorUnknown wrote:
    > I've written a database (Access mdb) front-end using Python/wxpython/and
    > ADO. However, the scope of the project has changed and I need to access
    > the same data on an MSSQL server. Also, the front-end needs to be cross-
    > platform (Windows and Linux).
    >
    > Does anyone have any suggestions on what database connectivity I should
    > use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
    > the best way to go.
    >
    > BTW, although I would love to move away from the MSSQL server, that's
    > not going to change.


    mxODBC works on Windows, Linux and quite a few other platforms.
    Apart from mxODBC you will need an ODBC driver that allows
    you to connect to the database (whereever it is running).

    On Windows this is a no-brainer since all decent databases
    come with a Windows ODBC driver.

    On other platforms, the commercial
    ODBC driver vendors are usually the best choice, but there are
    also a couple of alternatives such as the FreeTDS ODBC which allows
    connecting to MS SQL running on Windows, but whether these are
    suitable for your needs depends on what you plan to do with the
    database -- FreeTDS is not exactly high performance, nor very
    reliable. However, it is quite usable for simple queries.

    --
    Marc-Andre Lemburg
    eGenix.com

    Professional Python Software directly from the Source (#1, Nov 24 2003)
    >>> Python/Zope Products & Consulting ... http://www.egenix.com/
    >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
    >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

    ________________________________________________________________________

    ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
    M.-A. Lemburg, Nov 24, 2003
    #3
  4. SectorUnknown

    Jon Franz Guest

    > Another possibility is to see if an OR-thingie like PDO/SQLObject/...
    > actually helps for database abstraction. I suppose they'll get really
    > "fun" to use once you need advanced queries, though. Does anybody have
    > any real-life experience with any of these Python OR mappers?


    I have lots of experience with PDO - but it is not an OR mapper.
    It's an abstraction layer on top of the DB-API that adds functionality
    and attempts to make it easier to write your application to a single
    API. I guess you could say its the n-1th abstraction layer.

    PDO might be useful for SectorUnknown's needs.

    ~Jon Franz
    NeuroKode Labs, LLC
    Jon Franz, Nov 24, 2003
    #4
  5. SectorUnknown

    Joe Francia Guest

    SectorUnknown wrote:
    > I've written a database (Access mdb) front-end using Python/wxpython/and
    > ADO. However, the scope of the project has changed and I need to access
    > the same data on an MSSQL server. Also, the front-end needs to be cross-
    > platform (Windows and Linux).
    >
    > Does anyone have any suggestions on what database connectivity I should
    > use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
    > the best way to go.
    >
    > BTW, although I would love to move away from the MSSQL server, that's
    > not going to change.


    Besides mxODBC, I know of two others:

    Windows only:
    http://adodbapi.sourceforge.net/

    Cross-platform:
    http://sourceforge.net/projects/pymssql/

    Peace,
    Joe
    Joe Francia, Nov 24, 2003
    #5
  6. Re: Database connect / PDO

    I've been looking through the documentation for PDO, but it sounds like
    you still need mxODBC. Is this correct?

    See: http://sourceforge.net/docman/display_doc.php?docid=20024&group_id=
    86244#supported

    In article <>,
    says...
    > > Another possibility is to see if an OR-thingie like PDO/SQLObject/...
    > > actually helps for database abstraction. I suppose they'll get really
    > > "fun" to use once you need advanced queries, though. Does anybody have
    > > any real-life experience with any of these Python OR mappers?

    >
    > I have lots of experience with PDO - but it is not an OR mapper.
    > It's an abstraction layer on top of the DB-API that adds functionality
    > and attempts to make it easier to write your application to a single
    > API. I guess you could say its the n-1th abstraction layer.
    >
    > PDO might be useful for SectorUnknown's needs.
    >
    > ~Jon Franz
    > NeuroKode Labs, LLC
    >
    >
    >
    SectorUnknown, Nov 24, 2003
    #6
  7. SectorUnknown

    Jon Franz Guest

    Re: Database connect / PDO

    Yes, an underlying DBAPI driver is required, and mxODBC works...
    mostly (see below).
    I might recommend the adodbapi driver for use on the windows platform,
    but that still leaves mxODBC for linux.

    I may be wrong, but I think mxODBC module still doesn't provide all
    the values for the DBAPI .description fields - I know the field name
    is provided, but I don't know if the size info will be correct, or if a
    non-None value will be available for the other attributes.
    Thus, some of the Field object's member variables may be meaningless
    when used with mxODBC on your project.

    Does anyone know offhand when mxODBC will add this info? I think
    its the last (or one of the few) things holding it back from true DBAPI
    2.0 compliance.

    ~Jon Franz
    NeuroKode Labs, LLC


    ----- Original Message -----
    From: "SectorUnknown" <>
    To: <>
    Sent: Monday, November 24, 2003 3:47 PM
    Subject: Re: Database connect / PDO


    > I've been looking through the documentation for PDO, but it sounds like
    > you still need mxODBC. Is this correct?
    >
    > See: http://sourceforge.net/docman/display_doc.php?docid=20024&group_id=
    > 86244#supported
    >
    > In article <>,
    > says...
    > > > Another possibility is to see if an OR-thingie like PDO/SQLObject/...
    > > > actually helps for database abstraction. I suppose they'll get really
    > > > "fun" to use once you need advanced queries, though. Does anybody have
    > > > any real-life experience with any of these Python OR mappers?

    > >
    > > I have lots of experience with PDO - but it is not an OR mapper.
    > > It's an abstraction layer on top of the DB-API that adds functionality
    > > and attempts to make it easier to write your application to a single
    > > API. I guess you could say its the n-1th abstraction layer.
    > >
    > > PDO might be useful for SectorUnknown's needs.
    > >
    > > ~Jon Franz
    > > NeuroKode Labs, LLC
    > >
    > >
    > >

    >
    >
    Jon Franz, Nov 24, 2003
    #7
  8. Re: Database connect / PDO

    Jon Franz wrote:
    > Yes, an underlying DBAPI driver is required, and mxODBC works...
    > mostly (see below).
    > I might recommend the adodbapi driver for use on the windows platform,
    > but that still leaves mxODBC for linux.
    >
    > I may be wrong, but I think mxODBC module still doesn't provide all
    > the values for the DBAPI .description fields - I know the field name
    > is provided, but I don't know if the size info will be correct, or if a
    > non-None value will be available for the other attributes.


    mxODBC provides all .description values except display_size and
    internal_size (and this is allowed by the DB API standard). These
    two values are rarely of importance and if you absolutely need them
    they can also be queried using the catalog methods the mxODBC exposes.

    You should note however, that some ODBC database drivers try
    to be smart and "optimize" the return values that you see
    in .description (the MyODBC driver is a prominent example).
    While this is allowed by the ODBC standard, it is certainly
    not good practice.

    As a result, the only true source of the schema information
    are the catalog methods, e.g. .columns() available in mxODBC.
    These also provide much more information than is available in
    ..description.

    > Thus, some of the Field object's member variables may be meaningless
    > when used with mxODBC on your project.
    >
    > Does anyone know offhand when mxODBC will add this info? I think
    > its the last (or one of the few) things holding it back from true DBAPI
    > 2.0 compliance.


    mxODBC 2.0.x is 100% DB API 2.0 compliant.

    The only omissions are .nextset() and .callproc() which will be
    available in mxODBC 2.1.0. Both are optional in the DB API 2.0
    specification.

    > ~Jon Franz
    > NeuroKode Labs, LLC
    >
    >
    > ----- Original Message -----
    > From: "SectorUnknown" <>
    > To: <>
    > Sent: Monday, November 24, 2003 3:47 PM
    > Subject: Re: Database connect / PDO
    >
    >
    >
    >>I've been looking through the documentation for PDO, but it sounds like
    >>you still need mxODBC. Is this correct?
    >>
    >>See: http://sourceforge.net/docman/display_doc.php?docid=20024&group_id=
    >>86244#supported
    >>
    >>In article <>,
    >> says...
    >>
    >>>>Another possibility is to see if an OR-thingie like PDO/SQLObject/...
    >>>>actually helps for database abstraction. I suppose they'll get really
    >>>>"fun" to use once you need advanced queries, though. Does anybody have
    >>>>any real-life experience with any of these Python OR mappers?
    >>>
    >>>I have lots of experience with PDO - but it is not an OR mapper.
    >>>It's an abstraction layer on top of the DB-API that adds functionality
    >>>and attempts to make it easier to write your application to a single
    >>>API. I guess you could say its the n-1th abstraction layer.
    >>>
    >>>PDO might be useful for SectorUnknown's needs.
    >>>
    >>>~Jon Franz
    >>>NeuroKode Labs, LLC
    >>>
    >>>
    >>>

    >>
    >>

    >
    >


    --
    Marc-Andre Lemburg
    eGenix.com

    Professional Python Software directly from the Source (#1, Nov 24 2003)
    >>> Python/Zope Products & Consulting ... http://www.egenix.com/
    >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
    >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

    ________________________________________________________________________

    ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
    M.-A. Lemburg, Nov 25, 2003
    #8
  9. Thank you for your help.

    What I'm hearing though, is that there isn't an open source way to write an application for
    both Linux and Windows and have it access an MS-SQL server. Is this true?

    mxODBC requires a license and PDO appears to require mxODBC to run. (I work for a company and
    the program would be developed for internal use.)

    Is there another opportunity I'm missing?


    In article <>, says...
    > I've written a database (Access mdb) front-end using Python/wxpython/and
    > ADO. However, the scope of the project has changed and I need to access
    > the same data on an MSSQL server. Also, the front-end needs to be cross-
    > platform (Windows and Linux).
    >
    > Does anyone have any suggestions on what database connectivity I should
    > use? I've looked at mxODBC and wxODBC briefly, but am not sure what is
    > the best way to go.
    >
    > BTW, although I would love to move away from the MSSQL server, that's
    > not going to change.
    >
    SectorUnknown, Nov 25, 2003
    #9
  10. SectorUnknown

    Jon Franz Guest

    Re: Database connect / PDO

    > mxODBC provides all .description values except display_size and
    > internal_size (and this is allowed by the DB API standard). These
    > two values are rarely of importance and if you absolutely need them
    > they can also be queried using the catalog methods the mxODBC exposes.


    Sorry, but I disagree - these two values can be very important.

    > You should note however, that some ODBC database drivers try
    > to be smart and "optimize" the return values that you see
    > in .description (the MyODBC driver is a prominent example).
    > While this is allowed by the ODBC standard, it is certainly
    > not good practice.
    >
    > As a result, the only true source of the schema information
    > are the catalog methods, e.g. .columns() available in mxODBC.
    > These also provide much more information than is available in
    > .description.


    I can understand where you are coming from in that the drivers
    themselves may make it impossible to provide full/accurate column
    data from a query. I'd wager you can't even automate calls to
    ..column() because mxODBC doesn't necessarily know what table
    a column came from when results are fetched.

    I can only speak for myself, but it is quite frustrating to not get
    the information I need when I perform a query. Please realize
    that my message was not intended as a defacement or argument
    against mxODBC - I was simply warning the user of the pitfalls they
    may experience when using it with PDO.

    > mxODBC 2.0.x is 100% DB API 2.0 compliant.


    Then you should change your documentation :)
    "The mxODBC package provides a nearly 100% Python Database API 2.0 compliant
    interface "
    >From http://www.egenix.com/files/python/mxODBC.html


    > The only omissions are .nextset() and .callproc() which will be
    > available in mxODBC 2.1.0. Both are optional in the DB API 2.0
    > specification.


    If this is why the documentation says nearly, then your interpretation
    of what 100% would mean is different from mine. 100% compliant
    would, in my mind, be supporting all required interfaces. I wouldn't
    think optional interfaces are needed for compliance, and supporting
    them, although good, wouldn't come into the percentage... unless you
    wanted to say you were 105% compliant :) .Just my two cents.

    ~Jon Franz
    NeuroKode Labs, LLC
    Jon Franz, Nov 25, 2003
    #10
  11. Re: Database connect / PDO

    Jon Franz wrote:
    >>mxODBC provides all .description values except display_size and
    >>internal_size (and this is allowed by the DB API standard). These
    >>two values are rarely of importance and if you absolutely need them
    >>they can also be queried using the catalog methods the mxODBC exposes.

    >
    > Sorry, but I disagree - these two values can be very important.


    Can you present a use case ? display_size is predefined statically in
    ODBC:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappdpr_28.asp

    I can't think of any use case for internal size...

    >>You should note however, that some ODBC database drivers try
    >>to be smart and "optimize" the return values that you see
    >>in .description (the MyODBC driver is a prominent example).
    >>While this is allowed by the ODBC standard, it is certainly
    >>not good practice.
    >>
    >>As a result, the only true source of the schema information
    >>are the catalog methods, e.g. .columns() available in mxODBC.
    >>These also provide much more information than is available in
    >>.description.

    >
    > I can understand where you are coming from in that the drivers
    > themselves may make it impossible to provide full/accurate column
    > data from a query. I'd wager you can't even automate calls to
    > .column() because mxODBC doesn't necessarily know what table
    > a column came from when results are fetched.


    Well, the display_size could be hard-coded, but I don't
    see much a use... internal_size would be hard to figure out
    and is not worth the performance it costs. APIs like .gettypeinf()
    and .getinfo() can help you here, if you need more low-level
    information.

    > I can only speak for myself, but it is quite frustrating to not get
    > the information I need when I perform a query. Please realize
    > that my message was not intended as a defacement or argument
    > against mxODBC - I was simply warning the user of the pitfalls they
    > may experience when using it with PDO.
    >
    >>mxODBC 2.0.x is 100% DB API 2.0 compliant.

    >
    > Then you should change your documentation :)
    > "The mxODBC package provides a nearly 100% Python Database API 2.0 compliant
    > interface "
    >>From http://www.egenix.com/files/python/mxODBC.html


    Good catch :) I'll fix that. It was true for mxODBC 1.x.

    >>The only omissions are .nextset() and .callproc() which will be
    >>available in mxODBC 2.1.0. Both are optional in the DB API 2.0
    >>specification.

    >
    >
    > If this is why the documentation says nearly, then your interpretation
    > of what 100% would mean is different from mine. 100% compliant
    > would, in my mind, be supporting all required interfaces. I wouldn't
    > think optional interfaces are needed for compliance, and supporting
    > them, although good, wouldn't come into the percentage... unless you
    > wanted to say you were 105% compliant :) .Just my two cents.


    Hmm, I am the editor of the DB API 2.0 spec...

    A database package can be 100% compliant without implementing
    all optional features. The DB API spec was designed to allow
    this since otherwise some modules would never be able to
    call themselves compatible.

    --
    Marc-Andre Lemburg
    eGenix.com

    Professional Python Software directly from the Source (#1, Nov 24 2003)
    >>> Python/Zope Products & Consulting ... http://www.egenix.com/
    >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
    >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

    ________________________________________________________________________

    ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
    M.-A. Lemburg, Nov 25, 2003
    #11
  12. SectorUnknown

    Jon Franz Guest

    Re: Database connect / PDO

    > Can you present a use case ? display_size is predefined statically in
    > ODBC:
    >
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappdpr_28.asp
    >
    > I can't think of any use case for internal size...


    Variable length character (or binary) fields...
    I've written many a piece of code over the years that has had to
    create dynamic edit forms for a database. Variable length
    character fields are common place, and I've always found that
    enforcing the limit at edit time, rather than letting an error be
    raised or data be silently truncated, is a good practice.

    > Good catch :) I'll fix that. It was true for mxODBC 1.x.


    No worries.

    > > If this is why the documentation says nearly, then your interpretation
    > > of what 100% would mean is different from mine. 100% compliant
    > > would, in my mind, be supporting all required interfaces. I wouldn't
    > > think optional interfaces are needed for compliance, and supporting
    > > them, although good, wouldn't come into the percentage... unless you
    > > wanted to say you were 105% compliant :) .Just my two cents.

    >
    > Hmm, I am the editor of the DB API 2.0 spec...
    >
    > A database package can be 100% compliant without implementing
    > all optional features. The DB API spec was designed to allow
    > this since otherwise some modules would never be able to
    > call themselves compatible.


    That's exactly what I thought, and as my statement said, I was only trying
    to figure out why 'nearly' was used, and then argue against the use if
    all of the required features were already present. A typo/slip-up makes
    much more sense anyway. :)

    cheers.

    ~Jon Franz
    NeuroKode Labs, LLC
    Jon Franz, Nov 26, 2003
    #12
  13. Re: Database connect / PDO

    Jon Franz wrote:
    >>Can you present a use case ? display_size is predefined statically in
    >>ODBC:
    >>

    >
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappdpr_28.asp
    >
    >>I can't think of any use case for internal size...

    >
    > Variable length character (or binary) fields...
    > I've written many a piece of code over the years that has had to
    > create dynamic edit forms for a database. Variable length
    > character fields are common place, and I've always found that
    > enforcing the limit at edit time, rather than letting an error be
    > raised or data be silently truncated, is a good practice.


    Ah, now I understand: for VARCHAR columns, that information is
    available in the precision field of .descpription. It may sound
    like the wrong entry, but that's where ODBC put's this
    information.

    OTOH, what the optional description entries really mean is not
    defined anywhere in the DB API. Perhaps we should fix that (on
    the db-sig mailing list where these discussions usually happen) ?!

    --
    Marc-Andre Lemburg
    eGenix.com

    Professional Python Software directly from the Source (#1, Nov 26 2003)
    >>> Python/Zope Products & Consulting ... http://www.egenix.com/
    >>> mxODBC.Zope Database Adapter ... http://zope.egenix.com/
    >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

    ________________________________________________________________________

    ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
    M.-A. Lemburg, Nov 26, 2003
    #13
  14. SectorUnknown

    Jon Franz Guest

    Re: Database connect / PDO


    > Ah, now I understand: for VARCHAR columns, that information is
    > available in the precision field of .descpription. It may sound
    > like the wrong entry, but that's where ODBC put's this
    > information.


    Doh, it looks like you found my source of confusion though.

    > OTOH, what the optional description entries really mean is not
    > defined anywhere in the DB API. Perhaps we should fix that (on
    > the db-sig mailing list where these discussions usually happen) ?!


    I was ready to mention this today, I'll move this to the db-sig list.
    Good catch :)

    ~Jon Franz
    NeuroKode Labs, LLC
    Jon Franz, Nov 26, 2003
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. gpeacock

    ASP database connectivity KB253604

    gpeacock, Nov 5, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    331
    Michael Evanchik
    Nov 10, 2003
  2. Steven Caliendo

    MySQL database connectivity

    Steven Caliendo, Dec 17, 2003, in forum: ASP .Net
    Replies:
    6
    Views:
    432
    dryer
    Dec 18, 2003
  3. Niks

    Database Connectivity using DSN

    Niks, Dec 22, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    10,824
    Kevin Spencer
    Jan 12, 2004
  4. Arun K

    Database Connectivity using DSN

    Arun K, Dec 22, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    388
    Natty Gur
    Dec 23, 2003
  5. C Newby

    Database Connectivity Problem ...

    C Newby, Apr 16, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    305
    C Newby
    Apr 16, 2004
Loading...

Share This Page