Newbie SQL ? in python.

Discussion in 'Python' started by len, Aug 17, 2006.

  1. len

    len Guest

    I have tried both the pyodbc and mxODBC and with help from the ng been
    able to do what I want using either. My needs are pretty basic some
    simple selects and inserts.

    The current problem I have hit is the database I am inserting into have
    a special ODBC driver that using the files natively has an
    autoincrement feature. However, through the ODBC driver the
    autoincrement does not work. (The explanation I got was the creators
    did not anticapate a great need for insert.) Anyway, I figured not a
    problem I will just do a select on the table ordered by the ID field in
    descending order and fetch the first record and do the autoincrementing
    within the python program. The code , using pyodbc is as follows.

    c.execute("select state_sid from statecode order by state_sid DESC")
    sid = c.fetchone()
    newsid = sid.state_sid + 1

    This code works fine and I get what I want. My concern is that this
    technique used on large files may cause problem. I really just want to
    get what is the last record in the database to get the last ID used.

    Is there a better way. I realize this may be more of an SQL question
    but I figured I would try here first.

    Len Sumnler
    len, Aug 17, 2006
    #1
    1. Advertising

  2. len

    John Machin Guest

    len wrote:
    > I have tried both the pyodbc and mxODBC and with help from the ng been
    > able to do what I want using either. My needs are pretty basic some
    > simple selects and inserts.
    >
    > The current problem I have hit is the database I am inserting into have
    > a special ODBC driver that using the files natively has an
    > autoincrement feature. However, through the ODBC driver the
    > autoincrement does not work. (The explanation I got was the creators
    > did not anticapate a great need for insert.)


    The creators of what? The ODBC driver for the database? Care to tell us
    which database software this is?

    > Anyway, I figured not a
    > problem I will just do a select on the table ordered by the ID field in
    > descending order and fetch the first record and do the autoincrementing
    > within the python program. The code , using pyodbc is as follows.
    >
    > c.execute("select state_sid from statecode order by state_sid DESC")
    > sid = c.fetchone()
    > newsid = sid.state_sid + 1
    >
    > This code works fine and I get what I want.


    Are you sure? Have you tested what happens if somebody comes along
    after you and inserts some rows using the native auto-increment feature
    -- do their blahblah_sid numbers start where you finished or do they
    overlap with yours?

    > My concern is that this
    > technique used on large files may cause problem. I really just want to
    > get what is the last record in the database to get the last ID used.


    FWIW,
    c.execute("select max(state_sid) from statecode")
    should give you the same answer
    should not be slower
    may be faster

    > Is there a better way. I realize this may be more of an SQL question
    > but I figured I would try here first.


    Tell us which RDBMS software you are using, and someone who is familiar
    with that may be able to help you -- otherwise you'd better ask in a
    forum specialised to that RDBMS.

    You may find that you can't use ODBC at all to insert those rows. You
    may be restricted to using ODBC only to do some preparatory read-only
    checking work. To insert you will probably have two options:
    (a) use Python to write a script of SQL insert statements. Run this
    using a script runner tool that comes with the RDBMS.
    (b) use Python to write a file (typically one per table) of data rows
    in (e.g.) CSV format. Load this using a bulk-load-from-text-file tool
    that comes with the RDBMS.

    HTH,
    John
    John Machin, Aug 18, 2006
    #2
    1. Advertising

  3. On 17 Aug 2006 14:35:02 -0700, "len" <> declaimed the
    following in comp.lang.python:

    >
    > The current problem I have hit is the database I am inserting into have
    > a special ODBC driver that using the files natively has an


    Huh? ODBC is just a means of transferring SQL statements to a
    database engine without requiring a direct linkage to the native access
    library.

    > autoincrement feature. However, through the ODBC driver the
    > autoincrement does not work. (The explanation I got was the creators
    > did not anticapate a great need for insert.) Anyway, I figured not a


    What creators? ODBC would be pretty useless with being able to
    insert data.

    > problem I will just do a select on the table ordered by the ID field in
    > descending order and fetch the first record and do the autoincrementing
    > within the python program. The code , using pyodbc is as follows.
    >
    > c.execute("select state_sid from statecode order by state_sid DESC")
    > sid = c.fetchone()
    > newsid = sid.state_sid + 1
    >
    > This code works fine and I get what I want. My concern is that this
    > technique used on large files may cause problem. I really just want to
    > get what is the last record in the database to get the last ID used.
    >
    > Is there a better way. I realize this may be more of an SQL question
    > but I figured I would try here first.
    >


    It is not a Python or ODBC question, and barely an SQL one. It
    IS a question of the DBMS you are running -- which I think you still
    haven't told us. Heck, you are still talking "files" -- which could mean
    anything depending on the DBMS. At least be specific with: table,
    column, row_or_record (or, using the terms from relational database
    theory: relation, domain, tuple).

    The problem with your "solution" is that someone else could run
    the same operation and you both end up creating the SAME "newsid" value.

    Auto increment for primary keys is a function of the DBMS. M$
    JET has "autonumber", MySQL has "auto_increment", and SQLite has
    "integer primary key". Firebird does not have an "auto_increment" and
    requires one to create a "generator" and an "insert trigger". MaxDB uses
    a "serial" function defined as the default value to create the
    equivalent. As such, the interface used to connect to the DBMS should
    have no effect.

    MySQL, after an insert, allows one to invoke
    select last_insert_id()
    after the insert was performed (within the same transaction, I'd
    presume, to prevent some other transaction changing it on you).

    SQLite uses
    select last_insert_rowid()
    but is otherwise similar (note: the description of "integer primary key"
    indicates that it uses the maximum value already in the column, and adds
    1 to it, IDs could be reused if the highest records are deleted. Also,
    if the maximum is the largest valid integer, it attempts to find an
    unused number at "random").

    I never did learn what JET uses for this (a web search implies
    it is
    select @@IDENTITY
    ). For MaxDB it is
    select <table>.CURRVAL

    For Firebird, the recommendation seems to be to /first/ invoke
    the generator function manually,
    select generatorX()
    and use the value it returns as the ID on the insert itself. The insert
    trigger is okay when you /don't/ need to know the id of the inserted
    record (and should be written to only invoke the generator if the insert
    query doesn't supply a value for the ID).

    If you KNOW that the ID will always be the largest value in the
    column, AND that no other transaction can insert before you retrieve the
    value, then a simple
    select max(ID_column) from table
    might be all you need.
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Aug 18, 2006
    #3
    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. LenS
    Replies:
    0
    Views:
    565
  2. len

    Newbie Python SQL

    len, Aug 14, 2006, in forum: Python
    Replies:
    2
    Views:
    398
    Dennis Lee Bieber
    Aug 15, 2006
  3. ecoolone
    Replies:
    0
    Views:
    754
    ecoolone
    Jan 3, 2008
  4. João
    Replies:
    5
    Views:
    1,630
    pythonZen
    Mar 2, 2010
  5. Jules
    Replies:
    6
    Views:
    150
    Jules
    Jul 15, 2003
Loading...

Share This Page