project-python

Project Exercise with Python and MySQL:

$ pip install mysql-connector-python

import mysql.connector
con = mysql.connector.connect(
user = "ardit700_student",
password = "ardit700_student",
host = "108.167.140.122",
database = "ardit700_pm1database"
)

cursor = con.cursor()

word=input("Enter the word: ")

query = cursor.execute("SELECT Definition FROM Dictionary WHERE Expression = '%s'" % word)
results = cursor.fetchall()

if results:
    for result in results:
        print(result[0])
else:
    print("No word found!")

More SQL Statements

In the example you just saw, we used the following SQL statement in our Python code:

query = cursor.execute("SELECT * FROM Dictionary WHERE Expression = 'rain'")

That statement retrieved all the rows of the Dictionary table where
the value of the column Expression was rain. The string inside
cursor.execute() is SQL code that Python sends to the database. The
database understands that kind of language.

Here are some more examples of SQL queries that you can try out from
within your Python script just like we did previously:

Get all rows where the value of the column Expression starts with r:

"SELECT * FROM Dictionary WHERE Expression  LIKE 'r%'"

Get all rows where the value of the column Expression starts with rain:

"SELECT * FROM Dictionary WHERE Expression  LIKE 'rain%'"

All rows where the length of the value of the column Expression is less than four characters:

"SELECT * FROM Dictionary WHERE length(Expression) < 4"

All rows where the length of the value of the column Expression is four characters:

"SELECT * FROM Dictionary WHERE length(Expression) = 4"

All rows where the length of the value of the column Expression is greater than 1 but less than 4 characters:

"SELECT * FROM Dictionary WHERE length(Expression) > 1 AND length(Expression) < 4"

All rows of column Definition where the value of the column Expression starts with r:

"SELECT Definition FROM Dictionary WHERE Expression  LIKE 'r%'" 

Installing Pandas

Make sure you have pandas installed. You can install it with pip:

pip install pandas

or

pip3 install pandas

Also, in the next lecture, we will use an enhanced Python interactive shell called IPython.

IPython is just like the standard shell you get when you run Python, but IPython provides better printing for large text. This ability makes IPython suitable for data analysis because the program prints data in a well-structured format. You can install IPython with pip:

pip install ipython 

or

pip3 install ipython 

import pandas
df1 = pandas.read_csv("supermarkets.csv")

 import pandas
df2 = pandas.read_json("supermarkets.json")

Note on Loading Excel Files
In the next lecture, you’re also going to learn how to load Excel (.xlsx) files in Python with pandas. Pandas may require the xlrd library as a dependency. If you get an error such as ModuleNotFoundError: No module named ‘xlrd’, you can fix the error by installing xlrd:

pip install xlrd 

or

pip3 install xlrd 

set header row

 df8=pandas.Read_csv("data.txt", header= None)  
 df8

Set Column Names

df8.columns= ["ID", "Address", city", " ZIP"/"Country",  Employees)
df8

Set Column Names

df.set_index("id", inplace = true)

indexing and Slicing

df.loc["":"","":""]
list(df.loc["":"","":""])
df.iloc[1:3,1:3]
df.i*[3,4]

Deleting Columns and Rows

df = df.drop("322 hill",0)
df.drop(df.column[0:3],1)

Updating and Adding newColumns and Rows

len(df.index)
df["continent"]=df.shape[0]*["north america"]
df.shape
df["continent"] = df["country"] + "," +"north america"
转置
df = df7.T

Note on Nominatim
We are going to use Nominatim() in the next video. Nominatim() currently has a bug. To fix this problem, whenever you see these lines in the next video:

from geopy.geocoders import Nominatim
nom = Nominatim()

change them to these

from geopy.geocoders import ArcGIS
nom = ArcGIS()

The rest of the code remains the same.

Geocoding Addresses with Pandas and Geopy

from geopy.geocoders import Nominatim
nom = Nominatim()
n= nom.geocode("2995,ca 9426")
type(n)
df["coordinates"] = df["address].apply(nom.geocode)
df.coordinate[0]
df.coordinate[0].latitude
df["latitude"] = df["coordinate"].apply(lambda x: x.latitude if x != None else None)

Numpy

import numpy
n = numpy.arange(27)
n.reshape(3,3,3)
m = numpy.asarray([[123,12,123,33],[],[]])