SQL convert output from integer to float-CodePudding

The numbers in my results are of type integer, but I want to convert them to float.

import sqlite3
connection = sqlite3.connect("mydatabase.db")
my_cursor = connection.cursor()
my_cursor.execute("DROP TABLE IF EXISTS Weather")
my_cursor.execute("CREATE TABLE Weather(City, State, High, Low)")
my_cursor.execute("INSERT INTO Weather Values('Phoenix', 'Arizona', 105, 90)")
my_cursor.execute("INSERT INTO Weather Values('Tucson', 'Arizona', 101, 92)")
my_cursor.execute("INSERT INTO Weather Values('Flag Staff', 'Arizona', 105, 90)")
my_cursor.execute("INSERT INTO Weather Values('San Diego', 'California', 77, 60)")
my_cursor.execute("INSERT INTO Weather Values('Albuquerqu', 'New Mexico', 80, 72)")
my_cursor.execute("INSERT INTO Weather Values('Nome', 'Alaska', 64, -54)")

if __name__=="__main__":

    
    for i in my_cursor.execute("SELECT * FROM Weather"):
        print(i)

The output is:

('Phoenix', 'Arizona', 105, 90)
('Tucson', 'Arizona', 101, 92)
('Flag Staff', 'Arizona', 105, 90)
('San Diego', 'California', 77, 60)
('Albuquerqu', 'New Mexico', 80, 72)
('Nome', 'Alaska', 64, -54)

I am confused that how to covert the integer to float. After fixing, the output should be like:

('Phoenix', 'Arizona', 105.0, 90.0)
('Tucson', 'Arizona', 101.0, 92.0)
('Flag Staff', 'Arizona', 105.0, 90.0)
('San Diego', 'California', 77.0, 60.0)
('Albuquerqu', 'New Mexico', 80.0, 72.0)
('Nome', 'Alaska', 64.0, -54.0)

CodePudding user response：

Permission to answer, maybe you can try this:

CREATE TABLE weather (
  city VARCHAR(100),
  state VARCHAR(100),
  high DEC(4,1),
  low DEC(4,1)
  );

INSERT INTO weather VALUES('Phoenix', 'Arizona', 105, 90);
INSERT INTO weather VALUES('Tucson', 'Arizona', 101, 92);
INSERT INTO weather VALUES('Flag Staff', 'Arizona', 105, 90);
INSERT INTO weather VALUES('San Diego', 'California', 77, 60);
INSERT INTO weather VALUES('Albuquerqu', 'New Mexico', 80, 72);
INSERT INTO weather VALUES('Nome', 'Alaska', 64, -54);

SELECT * FROM weather;

Thank you...

CodePudding user response：

sqlite3 uses manifest typing

In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored.

The values can be stored as floats at INSERT time, eg:

INSERT INTO Weather Values('Phoenix', 'Arizona', 105.0, 90.0)

The values can be displayed as floats at SELECT time, eg:

SELECT city, state, high * 1.0, low * 1.0 from weather