Categories
MySQL Pandas Python Code

Get started with Pandas and MySQL

How to make a dataframe from your SQL database and query the data

This assumes you already have a sample database set up on your MySQL server and you have the username and password.

In the example shown we are logging on to a Raspberry Pi running MariaDB and we are executing a query to get 3408 properties into a dataframe.

We then run it in Python and view the results.

The Python code is as follows:

import mysql.connector
import pandas as pd
try:
cnx = mysql.connector.connect(user='user2', password='password2', host='192.168.1.9', database='immodb')
query = '''select * from imt1'''
SQL_Query = pd.read_sql_query(query, cnx)
df = pd.DataFrame(SQL_Query,columns=['title','price'])
print(df)
except mysql.connector.Error as err:
print(err)
else:
cnx.close()

Pandas demo - SQL Python code
Animation showing the minimum required code to connect to the database, query the table and convert into a Pandas dataframe to perform further analysis
pandas dataframe from MySQL
Pandas dataframe – from MySQL – using sort_values
Categories
email Python Code

Send email with Python

(and attachments)

If you have seen any tutorials online, beware of many that use port 465, port 587 is the correct port. Here we show the code you can use to send email and an attachment with this simple Python code.

It’s simple, as you don’t need to know MIME or “octet-stream” – you just need to include the code and add your own specific username, password, account details for Gmail.

Rather than include your password in the code, you can use “load_dotenv” instead. This especially important if you save your code on GitHub or similar.

You will need the following libraries installed in your Python environment: “smtplib“, “ssl” and “email

send_mail function
send_from and send_to
Also: Try using load_dotenv

# save user/pw info in .env in same dir

from dotenv import load_dotenv

load_dotenv()

https://github.com/RGGH/BeautifulSoup/blob/MySQL_version/emailer.py

If you would like to see this in action then you can watch the demonstration on “how to send email and attachments using python” on YouTube : https://youtu.be/yj9y4u04gaM

Categories
Python Code web scraping

Web Scraping with bs4

BeautifulSoup

Conditonal logic with soup.select

'''make a new list "ls_expose" and add hrefs to it if they contain 'expose' in the url.'''

for a in soup.select('a'):
            if 'expose' in a['href']:
                        ls_expose.append(a['href'])

Get all dropdown values from html

(using page saved locally, for testing)
from bs4 import BeautifulSoup
import requests

my_page = "Wohnung mieten im Umkreis von 50 km von Mannheim - Immob.html"

soup = BeautifulSoup(open(my_page),"lxml")
soup = soup.find('div',class_ = "select-input-wrapper")
items = soup.select('option[value]')
values = [item.get('value') for item in items]
textValues = [item.text for item in items]
print(textValues)

soup.select(‘option[value]’)

how to get dropdown option values with bs4
127 option values as seen in the html, we need to be able to get this with our code….

soup.select output
Output from script – this shows the values as text, they need to be converted to integers if you are going to use them in a for loop. See below for the code to do this, using a list comprehension.
 from bs4 import BeautifulSoup
 import requests

 my_page = "Wohnung mieten im Umkreis von 50 km von Mannheim - Immob.html"
 soup = BeautifulSoup(open(my_page),"lxml")
 soup = soup.find('div',class_ = "select-input-wrapper")
 items = soup.select('option[value]')
 values = [item.get('value') for item in items]
 x = [int(item) for item in values]
 print(x)

Note how I use soup.find to narrow down the find, otherwise I would have found other dropdowns as well