0

I have an sql table Tab 1 in a Database. It is ordered in descent way based on the date. This table has to be updated with new records from python. In python I have another table Tab 2 similar to this one (same column names) but new data again ordered in descent way as well. I need to copy/insert the records in Tab 2 into Tab 1. In particulat only those records which are new (it happens Tab 1 has records already present in Tab 2 and those do not have to be copied/inserted again)

In details, once these 2 tables are available, the code opens sql server and looping through the row of the Tab 2, it checks if that record is a new one. If YES, there is INSERT query which is supposed to place the new record at the first position of Tab 1. So that the final version of Tab 1 is still ordered in descent way (the new records should be at the beginning of Tab 1). However, this is not happening, the new records are added in the middle of Tab 1 or in some other position.

I tried using also INSERT TOP(1) to force this behaviour but is not working.

It should be an easy task for an expert (which I am not) to insert a new row in the first position of an existing SQL table, but I am stuck. Below the code:

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
df = pd.read_sql_query('SELECT * FROM dbo.Twitter',cnxn) #--> df is *Tab 1*, it is in sql server

for index, row in df5.iterrows(): #-->df5 is the python table, its records have to be inserted
    if str(row.id) not in df.id.values: #--> if they are new
        cursor.execute("INSERT INTO Twitter (id, Date_Time,Author,Tweet,Link, Source, Is_retwitted, Sentiment) values(?,?,?,?,?,?,?,?)", row.id, row.created_at, row.author, row.full_text, row.entities,row.source, row.is_retwitted, row.Sentiment) #-->QUERY
cnxn.commit()
cursor.close()

Many thanks in advance

Luigi87
  • 103
  • 2
  • First of all, which specific column or columns determine the descending order you are talking about? And are you positive that the rows you are adding are newer than any of the already existing rows? – Andriy M Aug 02 '21 at 11:09
  • the columns that determine the descending order is Date_Time. Yes I am positive because that is regulated by the if statement which checks on a unique id number (row.id). That has been tested on very small table to verify it. Simply the problem is the order – Luigi87 Aug 02 '21 at 12:10
  • Then could you please show how you are selecting the rows that you are seeing the newer rows in the middle rather than at the top? Would be preferable if you [edit] your question, as that seems to me an important detail to the problem description. – Andriy M Aug 02 '21 at 12:13
  • Sure I will edit. but sorry, I have not got exactly what you want me to show. A screenshot of the resulting erroneous table Tab 1 or something else? – Luigi87 Aug 02 '21 at 12:42
  • I'm assuming that in that part of your application where you are consuming your rows in the descending order, you are using a SELECT statement. So it would be helpful to see that SELECT statement. – Andriy M Aug 02 '21 at 13:26

1 Answers1

3

A table isn't ordered. There is no such thing as "first row", "next row" etc in a table.

You can have ORDER BY in a SELECT statement which defines the order of the result. Without ORDER BY, the DBMS is free to return the rows in any order it feels like doing, for the moment.

So, what you try to achieve is impossible, by itself.

You need some column and have the contents of that column determine what you define as "order". I.e., something you can use in your ORDER BY when you SELECT from that table.

Tibor Karaszi
  • 17,101
  • 2
  • 14
  • 26
  • thanks for your answer, is it possible then to modify my INSERT statement by adding the ORDER BY somewhere in the query? can you suggest me how to modify that query? thanks – Luigi87 Aug 02 '21 at 13:20
  • 4
    I think that you misunderstood my answer. There's no such thing as ORDER BY for an insert statement. A table isn't ordered. Add a column to the table and populate it with a value that you then can use in ORDER BY when you SELECT from that table. – Tibor Karaszi Aug 02 '21 at 13:25
  • 1
    @Luigi87: To try and put the right emphasis for you, you can't specify an order when adding rows to a table. But you can specify an order when reading/selecting rows from a table. – Andriy M Aug 02 '21 at 13:28
  • thanks guys for both answers. I understood that INSERT has no such a thing as ORDER BY however, my table 1 (the one in sql server) has to be ordered. A SELECT with a ORDER BY is just a temporary ordered visualisation of the original table. But once that query is off the table returns to be disordered. That is the reason why I have not used the SELECT but I wanted INSERT so that my table is ordered by nature..if I am forced to use SELECT then I would need to save output of that query and delete the disordered one..and I have to do this every time I update the table 1..my bad if I am not clear – Luigi87 Aug 02 '21 at 13:37
  • just for the sake of explanation..the reason why I want the original table Tab 1 to be ordered is that the users of such DB and table are not able to write queries, they want to see already the data ordered without having to run a query with ORDER BY – Luigi87 Aug 02 '21 at 13:47
  • 2
    If they are not able to write queries how can they see the data? If they are either able to write simple SELECT queries (in which case they can be taught to add the specific ORDER BY somecolumn DESC; to see them in the wanted order) or you can edit the application they use to see that data so the application has that ORDER BY. – ypercubeᵀᴹ Aug 02 '21 at 14:16
  • So, whatever the reason why you want the original table Tab 1 to be ordered, the task is impossible. Unless you/them/application/whoever-is-reading-the-table's-data provides an ORDER BY or the application sorts them after getting the data. – ypercubeᵀᴹ Aug 02 '21 at 14:21
  • 2
    To add further emphasis: "my table 1 (the one in sql server) has to be ordered". That is impossible. There are nu such things as ordered tables in an RDBMS (like SQL Server). Either chose some different type of DBMS or re-think your approach according to our recommendations. Sorry... – Tibor Karaszi Aug 02 '21 at 14:23
  • yes you are all right. My idea is then to create a GUI with options (WHERE types) and translate those options in queries. In the back, I will myself add the select statement with ORDER BY, which will run everytime they want to see the data – Luigi87 Aug 02 '21 at 14:29