Skip to content

Instantly share code, notes, and snippets.

@ctrlcctrlv
Created October 17, 2023 19:13
Show Gist options
  • Save ctrlcctrlv/56b5ccad23813b1fe5e7466fe45a30d2 to your computer and use it in GitHub Desktop.
Save ctrlcctrlv/56b5ccad23813b1fe5e7466fe45a30d2 to your computer and use it in GitHub Desktop.
sqlite3.pl
#!/usr/bin/perl
$0 =~ s@.*/@@;
sub usage {
my $usage = <<'END_USAGE';
_ _ _ _____ _
___ __ _| (_) |_ ___|___ / _ __ | |
/ __|/ _` | | | __/ _ \ |_ \ | '_ \| |
\__ \ (_| | | | || __/___) || |_) | |
|___/\__, |_|_|\__\___|____(_) .__/|_|
|_| |_|
This script is used to execute SQL queries on a SQLite database.
END_USAGE
$usage .= "\n\nUsage: $0 <dbfile> <query> <query_args>\n";
$usage .= "Example: $0 /tmp/test.db 'SELECT * FROM test_table WHERE id = ? AND name = ?' 1 'John Doe'\n";
print $usage;
exit 1;
}
use strict;
use warnings;
use utf8;
use Encode qw(decode encode);
utf8::decode($_) for @ARGV;
use open IO => ':utf8';
use DBI;
use SQL::Parser;
my $parser = SQL::Parser->new('Ansi');
# Set this to 1 to enable debug messages.
my $_SQLITE3_DEBUG;
if (defined $ENV{'SQLITE3_DEBUG'} && $ENV{'SQLITE3_DEBUG'} == 1) {
$_SQLITE3_DEBUG = 1;
} else {
$_SQLITE3_DEBUG = 0;
}
my $dbname = shift @ARGV;
if (!defined $dbname) {
usage();
}
my $dbpath = "dbi:SQLite:dbname=$dbname";
my $dbh = DBI->connect($dbpath, "", "", { RaiseError => 1 }) or die $DBI::errstr;
$dbh->do("PRAGMA foreign_keys = OFF");
$dbh->do("PRAGMA encoding = 'UTF-8'");
sub do_query {
my ($query, @query_args) = @_;
if (!defined $query) {
usage();
}
# Check if sqlite3 is installed.
if (!`which sqlite3`) {
print "sqlite3 not found, please install sqlite3\n";
exit 1;
}
if ($_SQLITE3_DEBUG == 1) {
print STDERR "Debug: Executing query:\n\t\t$query\n\t\tArguments: @query_args\n";
}
my $sth = $dbh->prepare($query);
$sth->execute(@query_args);
# Return rows if any in awk-able format.
while (my @row = $sth->fetchrow_array()) {
print join("\t", @row) . "\n";
}
$sth->finish();
}
my $sqli = shift @ARGV;
my @args = @ARGV;
do_query($sqli, @args);
$dbh->disconnect();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment