Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

sqlite show tables

by igoryonya (Pilgrim)
on Jul 19, 2021 at 04:32 UTC ( #11135153=perlquestion: print w/replies, xml ) Need Help??

igoryonya has asked for the wisdom of the Perl Monks concerning the following question:

I tried to show tables in SQLite database file, as in MySQL:
my $sth = $dbh->prepare('SHOW TABLES');
, but it gave me an error:

DBD::SQLite::db prepare failed: near "SHOW": syntax error

I searched on the internet and found this solution:
$sth = $dbh->prepare('pragma table_info(execution_host)');
It doesn't give me an error, but, after executing:
$sth->execute(); $sth->rows;
returns 0 rows.

How to list SQLite tables?

Replies are listed 'Best First'.
Re: sqlite show tables
by Corion (Pope) on Jul 19, 2021 at 07:22 UTC

    You want to use the DBI "catalog methods" to retrieve table information. That way, your code will also work on other databases than SQLite:

    use Data::Dumper; my $sth = $dbh->table_info; my $columns = $sth->fetchall_arrayref({}); print Dumper $columns;
Re: sqlite show tables
by Tux (Canon) on Jul 20, 2021 at 08:41 UTC

    Please do not compare database specific syntax. DBI is written to support the ANSI SQL syntax and not port any weird CLI command syntax, so .schema cannot be used through the DBI unless the driver (DBD::SQLite) has chosen to support that through a ->func call (which it did noet).

    $ perl -MDBI -wE'my$dbh=DBI->connect("dbi:SQLite:dbname=db.1");say for +$dbh->tables' "main"."sqlite_master" "temp"."sqlite_temp_master" "main"."foo" $ perl -MDBI -MData::Peek -wE'DDumper(DBI->connect("dbi:SQLite:dbname= +db.1")->table_info(undef,"main","foo",undef)->fetchall_arrayref({})); +' [ { REMARKS => undef, TABLE_CAT => undef, TABLE_NAME => 'foo', TABLE_SCHEM => 'main', TABLE_TYPE => 'TABLE', sqlite_sql => 'CREATE TABLE foo (c_foo integer not null +primary key, foo varchar (20))' } ] $ perl -MDBI -MData::Peek -wE'my $sth=DBI->connect("dbi:SQLite:dbname= +db.1")->prepare("select * from main.foo");$sth->execute;my@f=@{$sth-> +{NAME_lc}};my%ti;for$a(0..$#f){$ti{$f[$a]}{$_}=(@{$sth->{$_}})[$a]for + qw(NAME NAME_lc NAME_uc NULLABLE PRECISION SCALE TYPE)};DDumper\%ti; +' { c_foo => { NAME => 'c_foo', NAME_lc => 'c_foo', NAME_uc => 'C_FOO', NULLABLE => 0, PRECISION => undef, SCALE => undef, TYPE => 'integer' }, foo => { NAME => 'foo', NAME_lc => 'foo', NAME_uc => 'FOO', NULLABLE => 1, PRECISION => undef, SCALE => undef, TYPE => 'varchar (20)' } }

    These examples should work on every database (Oracle, Unify, MariaDB, SQLite, CSV, PostgreSQL, MySQL, Sybase, SQL-Server, Informix, Ingres, …), given that you use the correct arguments. e.g. SQLite's default schema is "main", where the default schema for PostgreSQL is "public".

    It is up to you to combine all this data into something that works for you. Don't expect PostgreSQL to fix the brainfarts and idotic quotation of mysql. Don't expect Oracle to supprt the meta-commands from the SQLite CLI. Don't expect Unify to support all data tapes supported in PostgreSQL. Etc etc. There is no ideal database, they all suffer something that makes them unusable in certain circumstances. If you start with "All databases such", it is easier to choose the one that sucks less than all the other options *for your project*. Sometimes that will be SQLite, sometimes it will be PostgreSQL, sometimes even Oracle. YMMV. (it also depends on the support of the database of your choice on the platform you work on Linux/Solaris/HP-UX/AIX/Windows/OSF-1/Android/iOS/... and even the underlying file-system (XFS, HFS, JFS, FAT, NTFS, Btrfs, Ext4, …)


    Enjoy, Have FUN! H.Merijn
      «…choose the one that sucks less…»

      They all suck. I’m pretty sure of this as in the famous last company I was with we supported MySQL, Oracle, Sybase and Microsoft SQL Server. One might say that this is/was yet another case of hubris. And this is true.

      «The Crux of the Biscuit is the Apostrophe»

Re: sqlite show tables
by dsheroh (Monsignor) on Jul 19, 2021 at 07:36 UTC
    Meta/structural things like listing all the tables are specific to each database engine, rather than a part of the common SQL standard. The closest sqlite equivalent I'm aware of is the .schema command, which will show you all of the table and index definitions in the database. You can also use .schema tablename to show only a single table definition.

    But, yes, in the DBI context, it's probably better to use the generic methods provided by DBI, as already suggested by Corion.

      Is it possible to execute .schema from the DBI or is it a cli client only thing?
        I don't actually know. I've never tried using .schema (or show tables with mysql) in DBI code, only in the interactive clients. When I've wanted to get a database's list of tables from DBI code, I've done it with my @tables = $dbh->tables(undef, undef, undef, 'TABLE'); (Documentation)
Re: sqlite show tables
by Marshall (Canon) on Jul 19, 2021 at 06:21 UTC
    SQLite has a master table which contains the structure of the DB.

    To list just the user generated actual tables (not index'es and excluding the statistic tables that SQLite maintains):

    Use a select statement like this (tested on one of my DB's using SQLite Manager):

    SELECT name FROM sqlite_master WHERE TYPE = 'table' AND name NOT LIKE 'sqlite_%';
    Prepare this select statement, execute it and fetch the resulting rows in the normal Perl way.

    For fun, SELECT * instead of tbl_name and see what all is in this thing. For example includes the SQL code that generated the table, etc.

    I like the SQLite Manager browser plugin, but for security reasons, it doesn't run on Chrome or Firefox anymore. So I installed Waterfox 64 bit browser just for the purpose of running this one plugin. I don't use Waterfox for any real browsing.

    Update: I don't know if there is a standard SQL way of getting this information or not? That select statement is specific to an SQLite DB. So it won't work on another DB. But this will get you what you want from SQLite.

Re: sqlite show tables
by NetWallah (Canon) on Jul 19, 2021 at 05:10 UTC
    Here are some working snippets (From old code I wrote) that may help you.

    This was intended to display to a web page using CGI - but you can strip that out.

                    "The difficult we do today; the impossible takes a little longer."

Re: sqlite show tables
by karlgoethebier (Abbot) on Jul 19, 2021 at 16:38 UTC
      This one doesn't show how to do it in perl.

        Sure. Nothing but the truth. But the friendly manual I linked to isn’t about Perl. It is about SQLite. With the according driver DBD::SQLite and DBI I guess it should work. Probably with something like this:

        use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","",""); my $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr); …;

        Uncomplete and not tested and it doesn’t compile, I know. And I suppressed or forgot the ugly details. As you like. The rest is left as an exercise to the reader.

        «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11135153]
Approved by marto
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2021-07-24 03:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?