SQL Database Connector
Python By Example
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].
PermalinkSimple Lottery Program
Learn By Example
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.
PermalinkPython Date
Calculate Date Duration in Python
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 daysPermalink
Import Faker
Easily add random text to your Python code
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 --userPermalink
Working with Python Strings
Some Practical Examples
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() )Permalink
Ordinal not in range
Easy Fix for this Common Python3 Error
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`Permalink
View Python Exceptions in the Browser
Quick trick to view errors
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
If an uncaught exception occurs, a detailed, formatted report will be displayed. The report includes a traceback showing excerpts of the source code for each level, as well as the values of the arguments and local variables to currently running functions, to help you debug the problem. Optionally, you can save this information to a file instead of sending it to the browser.
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.
PermalinkFormatted String Literals
Embed Variables in Strings
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:
## Display Date as Month, ##, #### (Without the leading zero in days.)
from datetime import date
print(f'Today is: {date.today().strftime("%B %-d, %G")}')
## Another way:
import datetime
dt = datetime.datetime.now()
print(f'{dt:%A} {dt:%B} {dt.day}, {dt.year}')
Another example, making sure that the display name is properly capitalized.:
Permalink## Make Name Uppercase content += """ Welcome to the Forum {FirstName.capitalize()}! """[1:-1]
Schedule
Wednesday | New England |
Thursday | Gluten Free |
Friday | Macintosh |
Saturday | Internet Tools |
Sunday | Open Topic |
Monday | Media Monday |
Tuesday | QA |
Other Posts
- SQL Database Connector
- cURL in Python
- Import Faker
- Working with Python Strings
- Python Date
- Simple Lottery Program
- Ghost Writing in Python
- PyTest Example
- Giving Thanks to Python
- Shuffle Text File
- Getting the Current URL Using Python in MacOS
- How pyperclip Can Supercharge Your Python Automation
- Ordinal not in range
- Using FFmpeg with Python
- Commify in Python