Python Mysql Connector

April 27, 2016October 13, 2017 admin1 Comment

Thought I would try my hand at some SQL programming with Python, I was stuck using a Windows machine(BLAH) I wanted to setup a MySQL database and 1 table for testing. I am on a Windows machine so I installed WAMP which is a Windows Apache Mysql Php server. You can get the installer for Windows here:
http://www.wampserver.com/en/

You can get the python library / module here:

Mysql Python
Using PhpMyAdmin I setup a database and added the table.

mysql

As you can see I created a function that added data into my new database. I pass it 4 parameters, id-name-dept-salary, when you execute this script the database gets populated with the correct data. Pretty cool!!

#!/usr/bin/python
from mysql import connector
import optparse

parser = optparse.OptionParser()
parser.add_option('-i', action="store", help="Enter Job ID#", type="int")
parser.add_option('-n', action="store", help="Enter Employee Name", type="string")
parser.add_option('-d', action="store", help="Enter Department", type="string")
parser.add_option('-s', action="store", help="Enter Salary", type="int")
options, args = parser.parse_args()

def databaseAdd(id,name,dept,salary):
    con = connector.Connect(user='jason',
                            password='password',
                            database='jason',
                            host='localhost')
    c = con.cursor()
    table_name = "employee"
    insert = "INSERT INTO " + table_name + " (id,name,dept,salary) \
    VALUES (%s, %s, %s, %s)"
    data_value = (id,name,dept,salary)
    c.execute(insert, data_value)
    con.commit()
    c.close()
    con.close()

databaseAdd(options.i ,options.n,options.d,options.s)

#!/usr/bin/python

from mysql import connector

import optparse

parser = optparse.OptionParser()

parser.add_option('-i', action="store", help="Enter Job ID#", type="int")

parser.add_option('-n', action="store", help="Enter Employee Name", type="string")

parser.add_option('-d', action="store", help="Enter Department", type="string")

parser.add_option('-s', action="store", help="Enter Salary", type="int")

options, args = parser.parse_args()

def databaseAdd(id,name,dept,salary):

con = connector.Connect(user='jason',

password='password',

database='jason',

host='localhost')

c = con.cursor()

table_name = "employee"

insert = "INSERT INTO " + table_name + " (id,name,dept,salary) \

VALUES (%s, %s, %s, %s)"

data_value = (id,name,dept,salary)

c.execute(insert, data_value)

con.commit()

c.close()

con.close()

databaseAdd(options.i ,options.n,options.d,options.s)

Here is how you can see the help and call the command properly:
help command

Here is what you can see when performing a select * from the table.
database command

Here is the awesome IDE pyCharm and the results from running the code.
pycharm
Here is an example of how to query that database from the table.

from mysql import connector
import sys
import optparse


parser = optparse.OptionParser()
parser.add_option('--id', action="store", help="Enter ID number", type="int")
options, args = parser.parse_args()


try:
    cnx = connector.Connect(user='jason',
                        password='password',
                        database='jason',
                        host='localhost')
except connector.Error as err:
    print "Connection to Database Failed"
    print err
    sys.exit(1)


cursor = cnx.cursor()
query = "SELECT * from employee WHERE id='%s'" % options.id
cursor.execute(query)
for row in cursor:
    print row

from mysql import connector

import sys

import optparse

parser = optparse.OptionParser()

parser.add_option('--id', action="store", help="Enter ID number", type="int")

options, args = parser.parse_args()

try:

cnx = connector.Connect(user='jason',

password='password',

database='jason',

host='localhost')

except connector.Error as err:

print "Connection to Database Failed"

print err

sys.exit(1)

cursor = cnx.cursor()

query = "SELECT * from employee WHERE id='%s'" % options.id

cursor.execute(query)

for row in cursor:

print row

Here is the output of the fetch command using ID as a parameter:
database command

Took me a while to figure out the syntax, so hopefully this helps someone.
Jason

Nagios Python Plugin Check Lock File Exists

April 23, 2016November 22, 2016 adminLeave a comment

#!/usr/bin/python

import os.path
import time
import datetime
import sys
import optparse

"""Check the status of application lock file to be sure it is not stale, warn at critical at 18000 seconds warn at 10800"""
parser = optparse.OptionParser()
parser.add_option('-w', action="store", default="10800", help="seconds to warn", type="int")
parser.add_option('-c', action="store", default="18000", help="seconds to crit", type="int")
options, args = parser.parse_args()

nagOk = 0
nagWarn = 1
nagCrit = 2

try:
    statInfo = os.stat('/opt/application/.sync-lock')
except OSError:
    print("WAITING FOR LOCK FILE")
    sys.exit(nagOk)

now = int(time.time())
statInfoSlice = statInfo[8]
timeDiff = now - statInfoSlice
#print("NOW: %s") % now
#print("FILEAGE: %s") %  statInfoSlice
#print("TIMEDIFF: %s") % timeDiff

if timeDiff > options.c:
    print("CRIT: lock file is older than 5 hours")
    sys.exit(nagCrit)
elif timeDiff > options.w:
    print("WARN: lock file is older than 3 hours")
    sys.exit(nagWarn)
else:
    print("OK: lock file is under 3 hours")
    sys.exit(nagOk)

#!/usr/bin/python

import os.path

import time

import datetime

import sys

import optparse

"""Check the status of application lock file to be sure it is not stale, warn at critical at 18000 seconds warn at 10800"""

parser = optparse.OptionParser()

parser.add_option('-w', action="store", default="10800", help="seconds to warn", type="int")

parser.add_option('-c', action="store", default="18000", help="seconds to crit", type="int")

options, args = parser.parse_args()

nagOk = 0

nagWarn = 1

nagCrit = 2

try:

statInfo = os.stat('/opt/application/.sync-lock')

except OSError:

print("WAITING FOR LOCK FILE")

sys.exit(nagOk)

now = int(time.time())

statInfoSlice = statInfo[8]

timeDiff = now - statInfoSlice

#print("NOW: %s") % now

#print("FILEAGE: %s") % statInfoSlice

#print("TIMEDIFF: %s") % timeDiff

if timeDiff > options.c:

print("CRIT: lock file is older than 5 hours")

sys.exit(nagCrit)

elif timeDiff > options.w:

print("WARN: lock file is older than 3 hours")

sys.exit(nagWarn)

else:

print("OK: lock file is under 3 hours")

sys.exit(nagOk)

PYTHON – Script to download youtube videos for offline viewing

April 3, 2016April 24, 2016 adminLeave a comment

I was interested in viewing this video of a news conference (USENIX 2016) on my trip home on Metro North Train, NYC => CT. The trip is about an hour an 10 minutes from Manhattan’s Grand Central Terminal to Milford CT, express train that is. My concern was that I would have choppy internet service on the way since I recently updated my laptop and the built in Verizon Mobile card was not activated yet. I would need to use my ATT iPhone as a hotspot, which proved to be very shakey at times. A colleague of mine recommended a website for making youtube videos available for offline viewing. The name of this site was:

http://www.keepvid.com

Right off the rip I was concerned that this site was infested with malware and any other bullshit associated with a free video ripping service. I used the site and was able to create a download of the video I was interested in, however who knows how sick my Windows based machine just got. I could of contracted anything from this site.

I thought about this and said, there has to be a better way, or a python lib for this, and low and behold a search came up with PYTUBE:
https://github.com/nficano/pytube

This library had some interesting features and literally blew away the keepvid site in regards to flexibility. Here is some explaining of what this library can do. Please have a look at the examples below, I will do my best to narrate them.

Here I use PIP to install the PYTUBE lib, you can ignore the DEPRECATION: warning for my outdated python that blares at you for being such an idiot.

[root@jasonralph ~]# pip install pytube
DEPRECATION: Python 2.6 is no longer supported by the Python core team, please upgrade your Python. A future version of pip will drop support for Python 2.6
Collecting pytube
  Using cached pytube-6.1.8.tar.gz
Installing collected packages: pytube
  Running setup.py install for pytube ... done
Successfully installed pytube-6.1.8

[root@jasonralph ~]# pip install pytube

DEPRECATION: Python 2.6 is no longer supported by the Python core team, please upgrade your Python. A future version of pip will drop support for Python 2.6

Collecting pytube

Using cached pytube-6.1.8.tar.gz

Installing collected packages: pytube

Running setup.py install for pytube ... done

Successfully installed pytube-6.1.8

Next up you can see that I am setting a variable yt(this is the video you want to download). Using python’s Pretty Print Lib you can run the pprint(yt.get_videos() method to see what formats are available for download.

Please have a look at the comments in the code for a bit more details in regards to what is going on, in this example I am using the filename Pulp_Fiction.mp4 for my filename I want to be when downloaded.

[jralph@jasonralph ~]$ cat py_video_downloader.py
from pytube import YouTube
from pprint import pprint

yt = YouTube("http://www.youtube.com/watch?v=Ik-RsDGPI5Y")

pprint(yt.get_videos())

print(yt.filename)

yt.set_filename('Pulp_Fiction.mp4')

# Notice that the list is ordered by lowest resolution to highest. If you
# wanted the highest resolution available for a specific file type, you
# can simply do:
print(yt.filter('mp4')[-1])
# <Video: H.264 (.mp4) - 720p>

# You can also get all videos for a given resolution
pprint(yt.filter(resolution='720p'))


video = yt.get('mp4', '720p')

# NOTE: get() can only be used if and only if one object matches your criteria.
# for example:

pprint(yt.videos)


video.download('/home/jralph/')

[jralph@jasonralph ~]$ cat py_video_downloader.py

from pytube import YouTube

from pprint import pprint

yt = YouTube("http://www.youtube.com/watch?v=Ik-RsDGPI5Y")

pprint(yt.get_videos())

print(yt.filename)

yt.set_filename('Pulp_Fiction.mp4')

# Notice that the list is ordered by lowest resolution to highest. If you

# wanted the highest resolution available for a specific file type, you

# can simply do:

print(yt.filter('mp4')[-1])

# <Video: H.264 (.mp4) - 720p>

# You can also get all videos for a given resolution

pprint(yt.filter(resolution='720p'))

video = yt.get('mp4', '720p')

# NOTE: get() can only be used if and only if one object matches your criteria.

# for example:

pprint(yt.videos)

video.download('/home/jralph/')

Ok so here is what it looks like when you execute the program:

[23:32:59] jralph@jasonralph:~ $ python py_video_downloader.py
[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,
 <Video: MPEG-4 Visual (.3gp) - 240p - Simple>,
 <Video: Sorenson H.263 (.flv) - 240p - N/A>,
 <Video: H.264 (.mp4) - 360p - Baseline>,
 <Video: H.264 (.mp4) - 720p - High>,
 <Video: VP8 (.webm) - 360p - N/A>]
Pulp Fiction - Dancing Scene
<Video: H.264 (.mp4) - 720p - High>
[<Video: H.264 (.mp4) - 720p - High>]
/usr/lib/python2.6/site-packages/pytube/api.py:141: DeprecationWarning: videos property deprecated. Use `get_videos()` instead.
  "instead.", DeprecationWarning)
[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,
 <Video: MPEG-4 Visual (.3gp) - 240p - Simple>,
 <Video: Sorenson H.263 (.flv) - 240p - N/A>,
 <Video: H.264 (.mp4) - 360p - Baseline>,
 <Video: H.264 (.mp4) - 720p - High>,
 <Video: VP8 (.webm) - 360p - N/A>]

[23:32:59] jralph@jasonralph:~ $ python py_video_downloader.py

[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,

<Video: MPEG-4 Visual (.3gp) - 240p - Simple>,

<Video: Sorenson H.263 (.flv) - 240p - N/A>,

<Video: H.264 (.mp4) - 360p - Baseline>,

<Video: H.264 (.mp4) - 720p - High>,

<Video: VP8 (.webm) - 360p - N/A>]

Pulp Fiction - Dancing Scene

<Video: H.264 (.mp4) - 720p - High>

[<Video: H.264 (.mp4) - 720p - High>]

/usr/lib/python2.6/site-packages/pytube/api.py:141: DeprecationWarning: videos property deprecated. Use `get_videos()` instead.

"instead.", DeprecationWarning)

[<Video: MPEG-4 Visual (.3gp) - 144p - Simple>,

<Video: MPEG-4 Visual (.3gp) - 240p - Simple>,

<Video: Sorenson H.263 (.flv) - 240p - N/A>,

<Video: H.264 (.mp4) - 360p - Baseline>,

<Video: H.264 (.mp4) - 720p - High>,

<Video: VP8 (.webm) - 360p - N/A>]

As you can see we have a new filename with the video we asked for to watch without a streaming internet connection, here is a ls to show:

[23:33:02] jralph@jasonralph:~ $ ls -ltr
total 32352
drwxr-xr-x 2 root   root       4096 Mar 20 22:50 image_staging
drwxr-xr-x 2 root   root       4096 Mar 20 22:51 JR.ORG_SITE_BACKUPS
-rwxrwxr-x 1 jralph jralph      691 Apr  3 00:34 py_video_downloader.py
-rw-rw-r-- 1 jralph jralph 33113243 Apr  3 23:33 Pulp_Fiction.mp4.mp4

[23:33:02] jralph@jasonralph:~ $ ls -ltr

total 32352

drwxr-xr-x 2 root root 4096 Mar 20 22:50 image_staging

drwxr-xr-x 2 root root 4096 Mar 20 22:51 JR.ORG_SITE_BACKUPS

-rwxrwxr-x 1 jralph jralph 691 Apr 3 00:34 py_video_downloader.py

-rw-rw-r-- 1 jralph jralph 33113243 Apr 3 23:33 Pulp_Fiction.mp4.mp4

As always, I am sure there are better ways to do this and I am sure there is cleaner code. Most of this code was taken right from the authors site who is a badass, here is his link:

https://github.com/nficano/pytube

Hope you liked,
J$0N

Daily Weather Email – PYTHON – CRON – API

April 1, 2016April 28, 2016 adminLeave a comment

Jason R. Ralph

Linux All Day Everyday

Month: April 2016

Python Mysql Connector

Nagios Python Plugin Check Lock File Exists

PYTHON – Script to download youtube videos for offline viewing

Daily Weather Email – PYTHON – CRON – API