-2

Hello I am writing a script to connect to an oracle db using cx_oracle module, and when I insert into my table its just inserting randomly. I was hoping it would just append it to the very end of the table.

My insert statement is below

query = "INSERT INTO CARDS(DATE,HOUR,TOTAL_CARDS,APPROVAL_RATE,PEND_RATE) VALUES('"+Ret[0]+"','" +Ret[1]+"','" +Ret[2]+"','" +Ret[3]+"','" +Ret[4]+"','")

it just inserts randomly I just want it to append to the very end of the table so it goes in order by HOUR. I dont have a primary key in the table is there a work around without using one?

is it possible to order the table by hour first then insert? would that work?

stack flow
  • 75
  • 6
  • 3
    "is it possible to order the table by hour first then insert? would that work?" That's not how SQL works. The table has no inherit order (or if it does, it will be an implementation detail that should not be relied upon). If you want order you have to use `ORDER BY` clause when `SELECT`ing – DeepSpace Nov 14 '19 at 15:13
  • Also, you may want to fix the [SQL injection](https://en.wikipedia.org/wiki/SQL_injection) vulnerability you have by using a parameterized query. – DeepSpace Nov 14 '19 at 15:15
  • Python does not do anything here. It just sends the SQL to oracle. The rest is fine by the database. Furthermore, there is no such thing as the "end". If you want your data sorted when you read it, order it in the select sql. Also, you should have a primary key. If you don't know why, it does not matter why, just add it. – zvone Nov 14 '19 at 15:16
  • Does this answer your question? [INSERT with ORDER on Oracle](https://stackoverflow.com/questions/1339991/insert-with-order-on-oracle) – Popeye Nov 14 '19 at 15:31

2 Answers2

1

I dont have a primary key in the table

Well that's bad practice but anyway.

I just want it to append to the very end of the table so it goes in order by HOUR.

The database does not guarantee order of records in a table. A table is an abstraction over a tablespace which is another abstraction over a system disk (which itself is quite likely to be an abstraction over a lot of little disks).

The ordering of records only matters when we query the data. That is why the only mechanism for guaranteeing the order of records is the ORDER BY clause of a SELECT statement. In your case that would be 

select *
from cards
order by date, hour

And presumably nothing else.

APC
  • 144,005
  • 19
  • 170
  • 281
1

In the relational model, rows in a table are not ordered in any way, so the order they come out in a query can be unrelated to the order in which they were inserted, and, in fact, two consecutive queries are not guaranteed to return rows in the same order (unless you specify an ORDER clause).

If you want the rows to come out in a particular order, just add an ORDER BY to the select statement.

Simon
  • 36
  • 3