from pysqlite2 import dbapi2 as sqlite
try:
from sqlite import encode, decode
except ImportError:
import base64
sqlite.encode = base64.encodestring
sqlite.decode = base64.decodestring
else:
sqlite.encode = encode
sqlite.decode = decode
imagedata = open('myfile.jpg', "rb").read()
con = sqlite.connect("mydbfile")
cur = con.cursor()
cur.execute('INSERT INTO image(binarydata) VALUES (?)', (sqlite.encode(sqlite.Binary(imagedata)),))
con.commit()
con.close()
Tag Archive for SQLite
SQLite 3 insert binary image data with Python
Installing the SQLite3 Database Interface for Ruby
$ sudo gem install sqlite3-ruby -- --with-sqlite3-dir=/usr/local/bin
Installing SQLite3 in OsX Leopard
$ curl <a href="http://www.sqlite.org/sqlite-3.5.4.tar.gz" >http://www.sqlite.org/sqlite-3.5.4.tar.gz</a> | tar zx $ cd sqlite-3.5.4 $ ./configure --prefix=/usr/local $ make $ sudo make install #Check that SQLite is installed properly $ sqlite3 --version
AS3 Import XML To SQLite
/**
* This is for importing xml data to a SQLite table
* @param node xml node
* @param user the user whos data this is
*
*/
public function importPostXML( node:XMLNode, dUser:UserVO ):void
{
var query:String = "INSERT INTO posts (" +
"post_url," +
"post_title," +
"post_description," +
"post_tags," +
"post_shared," +
"post_timestamp," +
"post_replace," +
"delicious_user )" +
"VALUES ( " +
":post_url," +
":post_title," +
":post_description," +
":post_tags," +
":post_shared," +
":post_timestamp," +
":post_replace," +
":delicious_user)";
sqlImport = new SQLStatement();
sqlImport.sqlConnection = dbconn;
sqlImport.addEventListener( SQLEvent.RESULT, onSQLSave );
sqlImport.addEventListener( SQLErrorEvent.ERROR, onSQLError );
sqlImport.text = query;
sqlImport.parameters[":post_url"] = node.attributes.href;
sqlImport.parameters[":post_title"] = node.attributes.description;
sqlImport.parameters[":post_description"] = node.attributes.extended;
sqlImport.parameters[":post_tags"] = node.attributes.tag;
sqlImport.parameters[":post_shared"] = node.attributes.shared;
sqlImport.parameters[":post_timestamp"] = node.attributes.time;
sqlImport.parameters[":post_replace"] = "no";
sqlImport.parameters[":delicious_user"] = dUser.username;
sqlImport.execute();
trace( "Importing XML to SQLite Database" );
}
Access SQLite Database from SAS
/************************************************
* ACCESS SQLITE DATABASE FROM SAS *
* REQUIREMENT: *
* HAVE SQLITE ODBC DRIVER INSTALLED *
************************************************/
libname sqlite odbc complete = "dsn=SQLite;
Driver={SQLite3 ODBC Driver};
Database=C:foldermydatabase.db";
SMS/Email Weather Alert
<?php
$ch = curl_init();
$timeout = 5; // set to zero for no timeout
curl_setopt ($ch, CURLOPT_URL, 'http://www.weather.gov/alerts/wwarssget.php?zone=TNZ027');
curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
$contents = curl_exec($ch);
curl_close($ch);
if(!empty($contents)) {
$db = new SQLiteDatabase('noaa.db2');
/*
$time = time();
$db->query("BEGIN;
CREATE TABLE status ( id INTEGER PRIMARY KEY, timestamp );
INSERT INTO status (timestamp) VALUES($time);
COMMIT;");
*/
$result = $db->query('SELECT * FROM status WHERE id = 1 LIMIT 1',SQLITE_ASSOC); //SQLITE_NUM SQLITE_BOTH (Default)
$data = $result->current();
//echo $data['timestamp'];
$xml = new SimpleXMLElement($contents);
foreach($xml->channel->item as $item) {
if (strstr(trim($item->title),'Tornado Warning')) {
if($data['timestamp'] < (time()-1800)) { // Set time in seconds between alerts
$db->query("UPDATE status SET timestamp = ".time()." WHERE id = 1");
require("phpmailer/class.phpmailer.php"); //http://sourceforge.net/projects/phpmailer
$mail = new PHPMailer();
$mail->IsSMTP(); // set mailer to use SMTP
$mail->SMTPAuth = true; // turn on SMTP authentication
$mail->Username = "xxxx"; // SMTP username
$mail->Password = "xxxx"; // SMTP password
$mail->SetLanguage("en",dirname(__FILE__) . "/phpmailer/language/");
$mail->From = "alerts@johnself.com";
$mail->FromName = "John Self";
$mail->AddAddress("email1@internet.com");
//$mail->AddAddress("email2@internet.com"); // Add as many recipients as you want
$mail->IsHTML(false);
$mail->Subject = "WEATHER ALERT";
$mail->Body = "A TORNADO WARNING HAS BEEN ISSUED FOR DAVIDSON COUNTY";
$mail->AltBody = "A TORNADO WARNING HAS BEEN ISSUED FOR DAVIDSON COUNTY";
if(!$mail->Send())
{
echo "Message could not be sent.
";
echo "Mailer Error: " . $mail->ErrorInfo;
exit;
}
exit;
}
}
}
}
?>