Newbie SQL ? in python.

[len]

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

 

[John Machin]

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

 

[Dennis Lee Bieber]

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
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/