Home > Enterprise >  How to get default values on SQLite table
How to get default values on SQLite table

Time:07-25

I recently created a table for tax rates which stored the its rates. Instead of adding another line of code for executing values I initialized the value of the rates(in column) to its own default values such as this:

pC.execute("""CREATE TABLE IF NOT EXISTS TAXRatesDB (
    PROVrate REAL DEFAULT 0.02 NOT NULL,
    UNIONrate REAL DEFAULT 0.05 NOT NULL,
    LNRrate REAL DEFAULT 0.09 NOT NULL,
    MEDICblrate REAL DEFAULT 0.02 NOT NULL,
    MEDICabrate REAL DEFAULT 1600 NOT NULL)
""")

I was wondering if I can get the default value via fetch so I used this execute code:

pC.execute("""SELECT * FROM TAXRatesDB""")
taxrates = pC.fetchall()
print(taxrates)

I tried to ran it but I only get [] is there another way to fetch default data or I'm missing something on my code.

CodePudding user response:

You can get the default values of a table's columns with the table_info pragma:

SELECT name, dflt_value
FROM pragma_table_info('TAXRatesDB');

gives

name         dflt_value
-----------  ----------
PROVrate     0.02      
UNIONrate    0.05      
LNRrate      0.09      
MEDICblrate  0.02      
MEDICabrate  1600

Or if you want all the information returned by the pragma, use the statement form instead of the table-valued function form:

sqlite> PRAGMA table_info(TAXRatesDB);
cid  name         type  notnull  dflt_value  pk
---  -----------  ----  -------  ----------  --
0    PROVrate     REAL  1        0.02        0 
1    UNIONrate    REAL  1        0.05        0 
2    LNRrate      REAL  1        0.09        0 
3    MEDICblrate  REAL  1        0.02        0 
4    MEDICabrate  REAL  1        1600        0 
  • Related