[Tutor] How to test for the existence of a table in a sqlite3 db?

[Flynn, Stephen (L & P - IT)]

Yep - oracle for example has hundreds of tables which hold metadata.


for example (this will look terrible due to the length of it but if you assume it's 3 lines; a title line, some underlining for each column and the data itself):


SQL> select * from all_tables where owner = 'FLYNNS' and table_name = 'QC11483';

OWNER                          TABLE_NAME                     TABLESPACE_NAME                CLUSTER_NAME                   IOT_NAME                       STATUS     PCT_FREE   PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS LOGGING BACKED_UP   NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE     INSTANCES  CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE     TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION        SKIP_CORRUPT MONITORING CLUSTER_OWNER                  DEPENDENCIES COMPRESSION DROPPED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------- --------- ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- ---------- ----------- ------------- ----------- ------------ --------- --------- ------ ----------- ------------ ------------ ---------- --------------- ------------ ---------- ------------------------------ ------------ ----------- -------
FLYNNS                         QC11483                        USERS                                                                                        VALID            10                     1        255          65536                       1  2147483645                                         NO      N              61517       1000            0          0          0         108                         0                   0          1          1     N ENABLED          61517 30/08/2017 16 NO                       N         N         NO     DEFAULT     DISABLED     YES          NO                         DISABLED     YES                                       DISABLED     DISABLED    NO

Executed in 0.141 seconds


There's something similar for SQL Server, DB2, ADDABAS, PostGres,, and so forth

S.

________________________________
From: Tutor <tutor-bounces+steve.flynn=capita.co.uk at python.org> on behalf of boB Stepp <robertvstepp at gmail.com>
Sent: 14 October 2017 08:17
To: tutor
Subject: Re: [Tutor] How to test for the existence of a table in a sqlite3 db?

On Sat, Oct 14, 2017 at 2:11 AM, boB Stepp <robertvstepp at gmail.com> wrote:

> So I get "None" as a result if the target table has not been created
> yet.  But if I *do* create the table I want:
>
> py3: with open('create_sqlite3_db.sql') as f:
> ...     sql = f.read()
> ...
> py3: c.executescript(sql)
> <sqlite3.Cursor object at 0x00000000026B4490>
> py3: tb_exists = "select name from sqlite_master where type='table'
> and name='BloodPressureReadings'"
> py3: tb_ck = c.execute(tb_exists).fetchone()
> py3: print(tb_ck)
> ('BloodPressureReadings',)
>
> So it is looking like I can use this technique to determine if I need
> to create the BloodPressureReadings table or not.  Am I on track here
> or is there a better technique?

It just occurred to me after sending the above, does something like
"sqlite_master" exist for other database programs than sqlite3?  Can I
abstract out "sqlite_master" and replace it with a variable so that I
can handle any kind of database?


--
boB
_______________________________________________
Tutor maillist  -  Tutor at python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor


This email is security checked and subject to the disclaimer on web-page: http://www.capita.co.uk/email-disclaimer.aspx