Newbie SQL ? in python.

[John Machin]

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