Torben at 20:44, 30 October 2024

2024-10-30T20:44:32Z

New page

[[Category:Coding]][[Category:Python]]
===Basics===
SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB.
Additinally date works as well

====Date and Time====
Use of CURRENT_TIMESTAMP variable
UPDATE outbox SET date_sent = CURRENT_TIMESTAMP WHERE date_sent IS NULL
Date select
DELETE FROM outbox WHERE date_created < date('now', '-1 month')

====VACUUM: Reduce Filesize after DELETE====
VACUUM
can also be run via sqlite3 command line tool
sqlite3 grafana.db 'VACUUM;'

===Using Python===
see [https://docs.python.org/3/library/sqlite3.html]

import sqlite3

# connect to DB
# con = sqlite3.connect('database_file.db')
con = sqlite3.connect(":memory:")
# con.row_factory = sqlite3.Row # allows for access via row["name"]
cur = con.cursor()

# create table
cur.execute('''
CREATE TABLE points
(date date, lat real, lon real, source text, name text)
''')

# ALTER TABLE
cur.execute("ALTER TABLE points ADD date_registered date")

# INSERT
sql = "INSERT INTO points(date) VALUES (?)"
cur.execute(sql, (datetime.date.today(),)) # here the "," is required if only one entry in tuple

sql = "INSERT INTO points(date, lat, lon) VALUES (?,?,?)"
cur.execute(sql, (myDate, myLat, myLon))
con.commit()

# UPDATE
sql = "UPDATE points SET lat = ? WHERE hash = ?"
cur.execute(sql, (myLat, h))
con.commit()

# SELECT 1 row
sql = "SELECT date FROM points WHERE hash = ? LIMIT 1"
row = cur.execute(sql, (h,)).fetchone()

# SELECT multiple rows
sql = "SELECT date, lat, lon FROM points ORDER BY email"
for row in cur.execute(sql):
print (row[0])
print (row["date"]) # if "con.row_factory = sqlite3.Row" is used

cur.close()
con.close()

===Using PHP===
$PATH = "/var/www/virtual/entorb/mail-daemon/outbox.db";
$db = new SQLite3($PATH);
$db->busyTimeout(10000);
$db->exec('BEGIN');
$stmt = $db->prepare('UPDATE outbox SET date_sent = CURRENT_TIMESTAMP WHERE date_sent IS NULL AND date_created = :date_created AND send_to = :send_to AND subject = :subject');
$stmt->bindValue(':date_created', $res['date_created']);
$stmt->bindValue(':send_to', $res['send_to']);
$stmt->bindValue(':subject', $res['subject']);
$result = $stmt->execute();
$db->exec('COMMIT');
$db->close();

===Using Perl===
use lib ( '/var/www/virtual/entorb/perl5/lib/perl5' );
my $PATH = "/var/www/virtual/entorb/mail-daemon/outbox.db";
use DBI;
my $dbh = DBI->connect( "dbi:SQLite:dbname=$PATH", "", "" );
$dbh->{ AutoCommit } = 0;
my $sth = $dbh->prepare( "INSERT INTO outbox(send_to, subject, body, send_from, send_cc, send_bcc, date_created, date_sent) VALUES (?, ?, ?, ?, '', '', CURRENT_TIMESTAMP, NULL)" );
$sth->bind_param( 1, $send_to, DBI::SQL_VARCHAR );
$sth->bind_param( 2, $subject, DBI::SQL_VARCHAR );
$sth->bind_param( 3, $body, DBI::SQL_VARCHAR );
$sth->bind_param( 4, $send_from, DBI::SQL_VARCHAR );
$sth->execute;
$dbh->commit;
$dbh->disconnect();

SQLite - Revision history

Torben at 20:44, 30 October 2024