I'm trying to update a table in PostgreSQL with Python, but there is something I don't quite get. The table contains 5 countries:
[(1,'Italy'), (2,'Poland'),(3,'South Korea'),(4,'Taiwan'),(5,'United States')]
When I update the table, say, replacing Poland with another country (e.g., Kazakhstan), the index shifts position to the bottom. So now you have:
[(1,'Italy'),(3,'South Korea'),(4,'Taiwan'),(5,'United States'),(2,'Poland')]
I also tried using ORDER BY, but then I get a syntax error. Why is that? And how can I maintain the index position?
Here is the code:
def update_countries():
return ("""
UPDATE countries
SET country_name = 'Spain'
WHERE country_id = 5;
""")
conn = None
try:
conn = psycopg2.connect(
database = 'social',
user = 'postgres',
password = 'abc123',
host = 'localhost',
port = '5432')
cur = conn.cursor()
country_table = update_countries()
cur.execute(country_table)
print("[ ] Data updated successfully!")
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print('{}[-] Failed to connect.'.format(error))
conn.rollback()
finally:
if conn is not None:
conn.close()
And here is the result:
Thank you in advance for taking the time to help.
CodePudding user response：
When you create the table, it is very recommended to create it along with indexing. Indexes are used to retrieve the records from the database more quickly and more orderly.
Indexes might help you to return the records by a query in a specific sorted order (primary key is one way to do indexing). This allows a query's ORDER BY specification to be met without a separate sorting step. The one indexing method that can return sorted output in PostgreSQL is B-Tree.
Below is the example of creating indexing:
CREATE INDEX country_asc_index ON countries (country_id ASC);
REF:
[2] https://www.postgresql.org/docs/current/indexes-ordering.html
CodePudding user response：
SQL tables have no order. Without an explicit ORDER BY in the SELECT statement, you are not even guaranteed the same order between two consecutive SELECT:s.