SQL Database Connector

It's really easy to query a database in Python. There's more than one way to do it, and there are plenty of third-party libraries that can offer better performance and cache capabilities.

Here's the basic to query a database:

hostname = 'localhost';
username = 'cryan';
password = 'Uniramous2';
database = 'webuser';
def doQuery( conn ) :
    cur = conn.cursor()
    content = ""
  
    cur.execute( "select date, title, comment from guestbook where topic = " + topic + "  order by title desc )
    records = cur.fetchall()
    for row in records:
        content = row[4]
    
    return content;    
myConnection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )
content += doQuery( myConnection )
myConnection.close()
    

Technical Notes

You have to set the hostname, username, password, and database before calling the mysql.connector.connect function.

For security you should set up a READ-ONLY user for public database reads. This way if the code is ever displayed, someone can't just write a simple query to wipe out the database.

I use a Python function to handle the queries. Obviously, this isn't required, but it's a good habit to get into.

cur.execute handles all the database interactions.

Using cur.fetchall() returns an array of entries, you need to call the specific row entry, for example: row[3].

Simple Lottery Program

One of the books that got me into programming was "Perl by Example" by Ellie Quigley. I learned a lot about how to perform certain tasks using simple examples.

This week's post is highlighting an example of how I learned using the book. The following is an example of creating a "Lottery Picker" Python application. This is a pretty basic example of using various Python functionality to generate a random set of numbers.

PowerBall Quick Pick

Players must select 5 numbers from 1 to 69. Then they must pick one additional number from a range of 1 to 26.

In this example, the program outputs 4 games. I use the sort list function to display the smaller numbers first.

import random
# Powerball Quick Pick
GameBalls = list(range(1,69))
PowerBall = list(range(1,26))
random.shuffle(GameBalls)
random.shuffle(PowerBall)
for x in range(4):
	mywin = GameBalls[0:5]
	mywin.sort()
	print(f"Winning Numbers: {mywin} with a Powerball number {PowerBall[0]}")
	random.shuffle(GameBalls)
	random.shuffle(PowerBall)

Sample Output

Winning Numbers: [2, 11, 15, 27, 58] with a Powerball number 10
Winning Numbers: [9, 54, 58, 63, 67] with a Powerball number 3
Winning Numbers: [10, 16, 18, 47, 62] with a Powerball number 4
Winning Numbers: [3, 35, 38, 41, 56] with a Powerball number 3

Odds of Winning Powerball

The odds of winning the PowerBall is 11,688,053 to 1. You have a one in 91 chance of matching at least 1 number.

Things This Code Demonstrates

The above piece of code answers some common "how do I" questions:

• How do I create a python list not starting from 0?
• How doI shuffle a list in Python?
• In Python, How do I create a new list from another list?
• How do I sort a randomize python list?
• After shuffling a list how do I sort it?

Future Site Content

I'll add a PowerBall number picker to the Random selection.

Python Date

Recently, I created a function to calculate the number of days from today. This simple function accepts a date object and returns the number of Years, Months, and Days between now and that event.

I used the Relativedelta object from the Date library. It's an easy way to do the converting with a few lines of code. Since Relativedelta is part of Python3, it will scale very nicely.

The Relativedelta type has a lot of the same attribute that is found in PHPs Date library.

In the example below I was able to convert a 10 line PHP function to 3 lines of code. I am sure there's even more refactoring that can be done.

#!/usr/bin/env /usr/bin/python3
from datetime import date
from datetime import datetime
from dateutil import relativedelta
NOW = datetime.now()
def datecal(someday):
	caldate = relativedelta.relativedelta(NOW,someday );
	return  f"{caldate.years} years, {caldate.months} months. {caldate.days} days";
day1 = datecal(date(1775,7,4))
	
print(day1)	
#  246 years, 5 months. 3 days

Import Faker

Faker is a useful Python package that generates fake data. There's no need to include lots of external text files to generate random data.

#!/usr/local/bin/python3
from faker import Faker
from faker.providers import currency
fake = Faker()
fake.add_provider(currency)
Faker.seed()
for _ in range(10):
	print(fake.pricetag())

Sample Output

$7,604.87
$975.93
$54.21
$89,241.15
$91,565.93
$1,784.08
$6.09
$4,535.13
$22.87
$5.87

Need Same Results?

If you are writing Unit Tests, you may want to have the same list generated. To do this, simply add a value to the seed().

# Keep the same data output:
Faker.seed(10)
# Go random for each run
Faker.seed()

Random Words

Faker has the ability to add random words. In this example, the variable "randomwords" has a list of 20 words, I then shuffle that list and present the winning word of the day:

from faker import Faker
import random
faker = Faker()
randomwords = faker.words(20)
random.shuffle(randomwords)
print(f'Winning Word of the day: {randomwords[1]}')
print(randomwords)

Output:

Winning Word of the day: country
['seven', 'country', 'prove', 'husband', 'cause', 'wide', 'son', 'probably', 'small', 'treatment', 'property', 'policy', 'in', 'along', 'husband', 'cup', 'news', 'partner', 'wish', 'should']

Using Faker Profile

If you need just some of the basic elements of random data, you can use a profile or simpleprofile.

profile() - job, company, ssn, residence, current_location, blood_group, website, username, name, sex, address, mail, birthdate

simple_profile() - username, name, sex, address, mail, birthdate

Exmple Use:

from datetime import date
from datetime import datetime
from dateutil import relativedelta
def mydatecal(someday):
	caldate = relativedelta.relativedelta(datetime.now(),someday );
	myformat = "{} years old".format(caldate.years);
	return myformat
	
customer=fake.simple_profile();
print(customer['name'] + ' has the following email: ' + customer['mail'])
print("Birthday: " + customer['birthdate'].strftime("%B %d, %G"))
print( mydatecal(customer['birthdate']) + " today."+ "n")

Sample Output

Michael Ramos has the following email: russellamy@hotmail.com
Birthday: September 06, 1956
65 years old today.

Installing Faker

Use PIP to install Faker

pip install Faker

If you're not able to install using the pip command, use this:

python3 -m pip install faker --user

Working with Python Strings

Various code snippets on using strings in Python.

Getting a File to a String

This is useful when you have a list in a file and you want to use it in your code. At the end of this snippet, I shuffle the new list.

f = open('~/random/content/namelist.txt', 'r');
surnames = f.read();
f.close();
surname = surnames.splitlines()
random.shuffle(surname)

Replacing different words with a single replacement

An easy way to search/replace multiple items:

# Quick Utility Function
def replace_all(text, dic):
    for i, j in dic.items():
        text = text.replace(i, j)
    return text
# Sample String
story = "author was set to move to state"
# dictionary with mixed keys
my_dict = {'author': 'John', 'state': 'Massachusetts'}
newstory = replace_all(story, my_dict)
print(newstory)

Use the String as a List

What if I wanted the 3rd word in a particular string?

# Sample string
mytext = "Pleached Baloney Silurus Ramble Chemolytic Bidentate Malanders Brugnatellite"
# Use Split to break the string
newtext = mytext.split()
# Now display the third word, keeping in mind the counter starts at zero
print(newtext[2])

Counting the number of List Elements

thekeywords= ["sum", "fast", "snow", "Sawyer", "Pond"]
totallines = len(thekeywords)
print(totallines)

Looping through an List to output data

In this example, I am splitting up a string and shuffling the words:

import random
samplequote = "Today is gonna be the day that they're gonna throw it back to you"
samplequote2 = samplequote.lower().split()
random.shuffle(samplequote2)
# Create a function to display the Array, useful if you want to do something other than printing.
def print_list_elements(list):
    for element in list:
        print(element)
print_list_elements(samplequote2)
# Quick and Easy Way:
print("Really easy way:" + str(samplequote2))
# Show it as a new String, capitalize the first word in the sentence:
print(' '.join(samplequote2).capitalize() )

Ordinal not in range

Have you ever encountered an error where the "Ordinal not in range?"

Sample Output:

UnicodeEncodeError: 'latin-1' codec can't encode characters in position 13821-13822: ordinal not in range(256)

According to various StackExachange comments, this error is thrown because Python can't encode some characters in a non Ascii string.

For me, this particular error occurred because I had bad data in my MySQL database - there was no codec checking done on INSERT. Apparently, PHP didn't have a problem handling the 'latin-1' codec - but using Python is now an issue.

Quick Easy Solution

My quick and easy solution is to have MySQL convert the text to ASCII. This is easy to do in-code:

cur.execute( "select date, CONVERT(teaserText USING ASCII), CONVERT(myTextData USING ASCII) from Order .... "")

Here's what happens: The MySQL CONVERT utility will convert the text in the teaserText and myTextData columns on the fly. The process is seemless and adds very little overhead while Python runs the query - your miles may vary if you have a complex database query.

Use Views

A better solution would be to create a view that would automatically convert the data, this way you can fix other columns if they cause issues:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `order_vw` AS
select
    date_format(`w`.`orderdate`, '%M %e, %Y') AS `date`, 
   convert(`w`.`teaserText`
        using ascii) AS `teaserText`,
    convert(`w`.`myTextData`
        using ascii) AS `myTextData`,
    `c`.`name` AS `item`,
from
    ((`order` `w`)
where
    (`w`.`weblog` <= curdate())
order by
    `w`.`weblog`

View Python Exceptions in the Browser

To enable debugging your Python CGI, use the following in your Python header:

import cgitb
cgitb.enable()

Now when you run into an error, you will see the error message in your browser. Just be careful not to enable this for production pages. cgitb is part of the standard distribution package.

Official Description

Note: This doesn't handle all errors, for example, if you put in an illegal condition statement, you'll see a syntax error in the browser, but will need to run the python script from the command line to see the error.

Turn Off When In Production!

cgitb is a good thing to enable when developing code. Once the code is complete, remove the cgitb reference so that if customers encounter an error they don't see the code.

Python Error page can show sensitive information!

Change the enable to display the data to a log file. Here's an example entry.

import cgitb
cgitb.enable(logdir=os.path.join(os.path.dirname(__file__), 'Weblog'),
             display=False,
             format='text',
             )

You can always setup a GitHub action to search for the "cgitb.enable()" before posting code into Production.

Formatted String Literals

One of the features I like in PHP is the heredoc string syntax because developers can include inline variables. This makes it easy to include variable within content.

Here's a sample snippet:

$content .= >>> end_here 
Hello, $world!
end_here

In Python 3, you have the same functionality by using f-strings (otherwise known as Formatted String Literals). To enable this you simply prefix the string with an 'f'. For example:

content += f"""
Hello {world}!
"""[1:-1]

or

print(f"Hello, {fname}, thank you for your input.")

Check out the Detailed Python Instructions on Formatted String Literals.

Remove the First Line

If you get in the habit of starting text on the line immediately below the start of the heredoc, you should add a [1:-1] at the end of the heredoc. This is because in Python the heredoc actually starts after the third quote. This means that if you use heredoc to generate lists, the first item will be empty. Yikes!

I would recommend setting up a code snippet with the [1:-1] at the end so you don't forget.

In this example: I created a list of restaurants using a heredoc, converted it to a list, randomized the list order, and then displayed the results:

foodchains = """
Taco Bell
Boston Market
MacDonalds
Sonic
Fatburger
Jack-In-The-Box
"""[1:-1]
fastfood = foodchains.splitlines()
random.shuffle(fastfood)
print(f'Best place to eat is {fastfood[1]} and {fastfood[2]} then {fastfood[3]}')

Fix the Unicode Error

If you encounter the dreaded "UnicodeEncodeError" from a database import, you can easily fix it by using a simple convert trick within your string. You can apply a modifier to the Formatted String to convert the value before it gets outputted.

In this example the display text will be convert to ascii():

print (f'Name: {databaseName!a})

Another useful format command is '!s' which will convert the text to string.

Adding Commas to Digits

When you have a large number and want to "commify" the output, simply use this trick with formatting string literals:

totalSales = 20000
print(f'Formal: {totalSales:,.2f} and Simple: {totalSales:,}')
// Formal: 20,000.00 and Simple: 20,000

Execute Expressions

You can easily perform commands within the f-string. In this example, I am displaying the current date:

Another example, making sure that the display name is properly capitalized.:

## Make Name Uppercase
content += """
Welcome to the Forum {FirstName.capitalize()}!
"""[1:-1]