How to correctly store floating point numbers in Sqlite so that it would be convenient to select from this data in the future?

Question

Python

Hello,

I am saving my list in Sqlite which contains floating point numbers (except for the first column which contains integers) into the following table:

cur.execute("""CREATE TABLE IF NOT EXISTS photos(id_photo INTEGER, \
h1 REAL, h2 REAL, h3 REAL, h4 REAL, h5 REAL, h6 REAL, h7 REAL, h8 REAL, h9 REAL, h10 REAL, \
h11 REAL, h12 REAL, h13 REAL, h14 REAL, h15 REAL, h16 REAL, h17 REAL, h18 REAL, h19 REAL, h20 REAL, \
h21 REAL, h22 REAL, h23 REAL, h24 REAL, h25 REAL, h26 REAL, h27 REAL, h28 REAL, h29 REAL, h30 REAL, \
h31 REAL, h32 REAL, h33 REAL, h34 REAL, h35 REAL, h36 REAL, h37 REAL, h38 REAL, h39 REAL, h40 REAL, \
h41 REAL, h42 REAL, h43 REAL, h44 REAL, h45 REAL, h46 REAL, h47 REAL, h48 REAL, h49 REAL, h50 REAL, \
h51 REAL, h52 REAL, h53 REAL, h54 REAL, h55 REAL, h56 REAL, h57 REAL, h58 REAL, h59 REAL, h60 REAL, \
h61 REAL, h62 REAL, h63 REAL, h64 REAL, h65 REAL, h66 REAL, h67 REAL, h68 REAL, h69 REAL, h70 REAL, \
h71 REAL, h72 REAL, h73 REAL, h74 REAL, h75 REAL, h76 REAL, h77 REAL, h78 REAL, h79 REAL, h80 REAL, \
h81 REAL, h82 REAL, h83 REAL, h84 REAL, h85 REAL, h86 REAL, h87 REAL, h88 REAL, h89 REAL, h90 REAL, \
h91 REAL, h92 REAL, h93 REAL, h94 REAL, h95 REAL, h96 REAL, h97 REAL, h98 REAL, h99 REAL, h100 REAL, \
h101 REAL, h102 REAL, h103 REAL, h104 REAL, h105 REAL, h106 REAL, h107 REAL, h108 REAL, h109 REAL, h110 REAL, \
h111 REAL, h112 REAL, h113 REAL, h114 REAL, h115 REAL, h116 REAL, h117 REAL, h118 REAL, h119 REAL, h120 REAL, \
h121 REAL, h122 REAL, h123 REAL, h124 REAL, h125 REAL, h126 REAL, h127 REAL, h128 REAL, h129 REAL, h130 REAL, \
h131 REAL, h132 REAL, h133 REAL, h134 REAL, h135 REAL, h136 REAL, h137 REAL, h138 REAL, h139 REAL, h140 REAL, \
h141 REAL, h142 REAL, h143 REAL, h144 REAL, h145 REAL, h146 REAL, h147 REAL, h148 REAL, h149 REAL, h150 REAL, \
h151 REAL, h152 REAL, h153 REAL, h154 REAL, h155 REAL, h156 REAL, h157 REAL, h158 REAL, h159 REAL, h160 REAL, \
h161 REAL, h162 REAL, h163 REAL, h164 REAL, h165 REAL, h166 REAL, h167 REAL, h168 REAL, h169 REAL, h170 REAL, \
h171 REAL, h172 REAL, h173 REAL, h174 REAL, h175 REAL, h176 REAL, h177 REAL, h178 REAL, h179 REAL, h180 REAL, \
h181 REAL, h182 REAL, h183 REAL, h184 REAL, h185 REAL, h186 REAL, h187 REAL, h188 REAL, h189 REAL, h190 REAL, \
h191 REAL, h192 REAL, h193 REAL, h194 REAL, h195 REAL, h196 REAL, h197 REAL, h198 REAL, h199 REAL, h200 REAL, \
h201 REAL, h202 REAL, h203 REAL, h204 REAL, h205 REAL, h206 REAL, h207 REAL, h208 REAL, h209 REAL, h210 REAL, \
h211 REAL, h212 REAL, h213 REAL, h214 REAL, h215 REAL, h216 REAL, h217 REAL, h218 REAL, h219 REAL, h220 REAL, \
h221 REAL, h222 REAL, h223 REAL, h224 REAL, h225 REAL, h226 REAL, h227 REAL, h228 REAL, h229 REAL, h230 REAL, \
h231 REAL, h232 REAL, h233 REAL, h234 REAL, h235 REAL, h236 REAL, h237 REAL, h238 REAL, h239 REAL, h240 REAL, \
h241 REAL, h242 REAL, h243 REAL, h244 REAL, h245 REAL, h246 REAL, h247 REAL, h248 REAL, h249 REAL, h250 REAL, \
h251 REAL, h252 REAL, h253 REAL, h254 REAL, h255 REAL, h256 REAL, \
FOREIGN KEY (id_photo) REFERENCES users (id));""")

How do I save:

cur.execute('''INSERT INTO photos(id_photo, \
h1, h2, h3, h4, h5, h6, h7, h8 , h9 , h10 , \
h11 , h12 , h13 , h14 , h15 , h16 , h17 , h18 , h19 , h20 , \
h21 , h22 , h23 , h24 , h25 , h26 , h27 , h28 , h29 , h30 , \
h31 , h32 , h33 , h34 , h35 , h36 , h37 , h38 , h39 , h40 , \
h41 , h42 , h43 , h44 , h45 , h46 , h47 , h48 , h49 , h50 , \
h51 , h52 , h53 , h54 , h55 , h56 , h57 , h58 , h59 , h60 , \
h61 , h62 , h63 , h64 , h65 , h66 , h67 , h68 , h69 , h70 , \
h71 , h72 , h73 , h74 , h75 , h76 , h77 , h78 , h79 , h80 , \
h81 , h82 , h83 , h84 , h85 , h86 , h87 , h88 , h89 , h90 , \
h91 , h92 , h93 , h94 , h95 , h96 , h97 , h98 , h99 , h100 , \
h101 , h102 , h103 , h104 , h105 , h106 , h107 , h108 , h109 , h110 , \
h111 , h112 , h113 , h114 , h115 , h116 , h117 , h118 , h119 , h120 , \
h121 , h122 , h123 , h124 , h125 , h126 , h127 , h128 , h129 , h130 , \
h131 , h132 , h133 , h134 , h135 , h136 , h137 , h138 , h139 , h140 , \
h141 , h142 , h143 , h144 , h145 , h146 , h147 , h148 , h149 , h150 , \
h151 , h152 , h153 , h154 , h155 , h156 , h157 , h158 , h159 , h160 , \
h161 , h162 , h163 , h164 , h165 , h166 , h167 , h168 , h169 , h170 , \
h171 , h172 , h173 , h174 , h175 , h176 , h177 , h178 , h179 , h180 , \
h181 , h182 , h183 , h184 , h185 , h186 , h187 , h188 , h189 , h190 , \
h191 , h192 , h193 , h194 , h195 , h196 , h197 , h198 , h199 , h200 , \
h201 , h202 , h203 , h204 , h205 , h206 , h207 , h208 , h209 , h210 , \
h211 , h212 , h213 , h214 , h215 , h216 , h217 , h218 , h219 , h220 , \
h221 , h222 , h223 , h224 , h225 , h226 , h227 , h228 , h229 , h230 , \
h231 , h232 , h233 , h234 , h235 , h236 , h237 , h238 , h239 , h240 , \
h241 , h242 , h243 , h244 , h245 , h246 , h247 , h248 , h249 , h250 , \
h251 , h252 , h253 , h254 , h255 , h256 ) \
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, \
    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, \
    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, \
    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, \
    ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', photo_data)

Before saving, just in case, I look / make sure what exactly is in my photo_data list (I left only the data from the first columns, since I already stepped over 10000 valid tostero characters):
List: ['7434343434333', 30.0, 47.0, 27.0, 41.0. .......]

Here is the most difficult moment for me.
After saving, I try to retrieve the previously saved data and understand that they are retrieved ONLY as a sequence of bytes:

row:  (7434343434333, b'\x00\x00\xf0A', b'\x00\x00<B', b'\x00\x00\xd8A', b'\x00\x00$B'.......)

That is, when saving, they were automatically converted to bytes (REAL), but when extracting, there is no reverse conversion, but my main task is to save a list containing floating point numbers to the base in order to further select from this table only using SQL queries (that is, do not select these bytes first and then try to convert them one by one to python-e):


That is, the task that a request of this type would work out, in fact, the request itself will be much more complicated.
SELECT * FROM photos WHERE h1 > 30.0

Answer 1

You did something wrong when creating the table. Your field type is not real, but BLOB, this is a binary type. That's why it produces such values. Perhaps before that they experimented and created a table with the BLOB type, so a new one with the REAL type was not recreated. Crash it and recreate it. Will produce normal types (real from sqlite should produce as float)
And at the same time, about the structure. Creating 256 fields for 256 values ​​does not look very good, and the convenience is dubious. It was better to write them down line by line, with numbering, i.e. table of the form:
(id_photo, h_num, h_value)
then the record will not look so monstrous, and it will also be convenient to request the desired value

select * from photos where id_photo = ... and h_num = 1
или
select * from photos where h_num = 1 and h_value > 30

Answer 2

List: ['7434343434333', 30.0, 47.0, 27.0, 41.0........]
instead of just multiplying by 10 to integers?