Connect to Sqlite3 using python

The below snippet allows you to create an sqlite3 database and then import data to it.

import sqlite3

#this connects and creates the database.db file, in the same location where the script exists

conn = sqlite3.connect(database.db)
c = conn.cursor()

#execute the create table
c.execute('''CREATE TABLE IF NOT EXISTS mytable
(filename text,
language text, 
year text,
summary text,
rating real)''')

#this is the dataset i will save to database as a python list:

data=['firstfile.mov','en','2018','This is a short summary of the video','3.8']

#get the data in the database
c.execute('INSERT INTO mytable VALUES (?,?,?,?,?)', data)

#close the connection
conn.commit()
conn.close()

Notepad++ Quick Trick #2 – Adding Line Breaks

My Quick Trick #2

Since you are editing text in Notepad ++, and you have already used Quick Tip #1 to mark specific lines of code, say containg <span> you might need to break the whole line in two lines so that a new line break forms after the <span> part of the code.

From this:

More Html Code here <span>Santa is Coming</span> More Html  Code here

To This:

More Html Code here <span>
Santa is Coming
</span> More Html  Code here

An easy way to do this by using the search and replace menu of notepad ++ . You need to seach for a part of the code where you want the break to occur, and then replace for the same part adding the escape sequence of the line break “\n” at the end. Make sure you have enabled “Extended Search Mode” at the bottom of the search window.

Adding Line Breaks

Additional Espace Sequences can be used in the same way:

 \" – double quote
 \\ – single backslash
 \a – bell/alert
 \b – backspace
 \r – carriage return
 \n – newline
 \s – space
 \t – tab

Nice?

 

Notepad ++ quick Trick #1 – Marking specific lines in code

Notepad ++ is my preferred text/ php/html/python editor, and, since some time i have been discovering all the nice things and automations you can do with it. I will be posting updates for notepad ++ automation and tricks, primary because its so hard for me to remember all of them. 🙂

Here it goes:

Trick #1

I some times need to scrape html code to specific tags or image urls. I found that this can be easily done using the search option of the Notepad++ and the “Mark” tab.

“Mark” basically bookmarks any line that has the search term in it. After you bookmark all the lines with “<span>” for example,  then you can go to Search> Bookmark>Remove Unmarked Lines  to remove all the parts of the code that do not contain the search term and of course are not needed. Nice?

htaccess force download file

Using the below part of code inside your htaccess file, you restrict the browser to download the file instead of displaying it in a seperate tab.

Two versions of the code, one for many filetypes and the second just only for pdf:

Various file types:

<FilesMatch "\.(?i:doc|odf|pdf|rtf|txt)$">
  Header set Content-Disposition attachment
</FilesMatch>

Only pdf:

<FilesMatch "\.(?i:doc|odf|pdf|rtf|txt)$">
  Header set Content-Disposition attachment
</FilesMatch>

taken form the drupal comnunity forum

reclaim SD card original size after having used it as operating system for Openelec

After having used one of my SD cards, in raspberry PI to host the operating system, i tried multiple times to restore it to its original size. It Only showed 125Mb of space while it should normally be around 4gb. Many utilities did not work, throwing errors. Disk part worked.

Open CMD in windows and type

diskpart

This lists all the drives registered in the system at the time and their original size (no partitions)

Select the drive (SD card) you want to reclaim space for.

Type : Select Drive #

Select Disk and the number of the drive, for me it was drive 2.

Type: Clean

Then navigate to windows disk manager, format the SD card with the file system you want FAT, FAT32 and assign it a drive letter.

Diskpart in windows
Reclaim SD card unused space

 

 

Update, below a complete set of commands since only clean does not seem to work always. Creating the primary partition and formating is needed after that.

SELECT DISK 1
CLEAN
CREATE PARTITION PRIMARY
SELECT PARTITION 1
ASSIGN
FORMAT FS=NTFS
EXIT

$’\r’: command not found

I usually crate my scripts on my windows everyday computer and upload them to my unix- bash running pc. Many times i get the error :$’\r’: command not found

This is caused by the “CRLF” End  of File for windows, which unix unfortunatelly does not like.

To correct this, open the script using Notepad++ and navigate to Edit>EOL Conversion>Unix(LF)

LF Notepad ++

And then retry the script.

This automatically converts all CRLF to LF. nice hah?

google charts using mysql recordset made easy

General

if your programming skills are limited, then you could use the below code to integrate google charts to your intranet or internet web site.

As i first step  i usually visit the the google charts page and select a chart that matches my desired outcome. Lets say that we have a list of cd collections that we want to process and we need to know graphically how many we have already processed or not. For that i would choose a pie chart.

Mysql Part

Next we need to retrieve the necessary data from the database using the appropriate query.

mysql_query

mysql_queryIn my case i only need to have two columns, one that shows the cd collection visibility type (1=visible, 0= hidden, 3=other status), and the count of each type of visibility.

PHP Part

Next, the php part of the mysql connection:

mysql_select_db($database_localhost, $localhost);
$query_Recordset1 = "select count(*) as count,visible from cdcollection where type=1 and serial=1 group by visible";
$Recordset1 = mysql_query($query_Recordset1, $localhost) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

then we transform the data to an array format:

$data[0] = array('visible','count'); 
 for ($i=1; $i<($totalRows_Recordset1+1); $i++)
 {
 $data[$i] = array(mysql_result($Recordset1, $i-1,'visible'),
 (int) mysql_result($Recordset1, $i-1, 'count'));
 }

Google Charts Script

The below script should be included in the <head> of your document in order to initialize the google charts library.

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

Then right below add the following script part, which basically transform  the data array to json and then it uses it to create the chart.

<script type="text/javascript"> 
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart1);
function drawChart1() {
var data = google.visualization.arrayToDataTable(<?php echo json_encode($data)?>);

 var options = {
 title: 'Collections',
 hAxis: {title: 'Year', titleTextStyle: {color: 'red'}},
 colors: ['#e0440e', '#e6693e', '#ec8f6e', '#f3b49f', '#f6c7b6']
 };


var chart = new google.visualization.PieChart(document.getElementById('chart_div1'));
 chart.draw(data, options);
</script>

I am explaining the red parts of the above code:

  • The json_encode($data) part transform the $data array to json. So in the mysql part if you used some other name for your dataset it should also be changed here.
  • The part right after “var options = {  “ includes all the settings of the chart. This is where you specify the title, the colour style, the fonts etc. Depending on what you need to do adequate documentation exists on the net..
  • Finally, the last red part PieChart is where you tell the api what chart you need it to render. This could be modified to ColumnChart or Histogram or any other form the available options you can find in the google chart development page.

See below the dashboard i have create for my self using three different recordsets.

My Google Charts Dashboard
My Google Charts Dashboard