(pip install mysql-connector-python) or similar library acts as middleware between
SQLite database connection and
OR SQL query
import sqlite3 (import the library)
connection = [Link](“database_name.db”) (connect to the database)
cursor = [Link]() (create a cursor object for SQL
commands)
[Link](“SELECT * FROM table_name”) (note SQL code is in “ “, within
the .execute())
# You can use any level of integration with pandas (upload from SQL then pure pandas, or
SQL language within a pandas dataframe)
# query to create a table named task1
[Link](''' CREATE TABLE task1
(FIND INT PRIMARY KEY NOT NULL,
FNAME TEXT NOT NULL,
COST INT NOT NULL,
WEIGHT INT);
''')
# insert query to insert food details in the above table
[Link]("INSERT INTO hotel VALUES (1, 'cakes',800,10 )")
[Link]("INSERT INTO hotel VALUES (2, 'biscuits',100,20 )")
[Link]("INSERT INTO hotel VALUES (3, 'chocos',1000,30 )")
print("All data in food table\n")
# create a cursor object for select query
cursor = [Link]("SELECT * from task1")
# display all data from hotel table
for row in cursor:
print(row)
# Like this we can: retrieve | alter | create | change structure | define schema (drop/alter/truncate) |
manipulate | grant/change access | define user functions and procedures | analyse manually | combine
datasets (inner/outer joins)
First, structure your database (if complex, using a VALUES
diagram is wise). For the examples below our (Cambridge, Massachusetts, 02139, blonde, 32,
database is: NewEngland and tables within are Jane, Doe)
people_maine, people_connecticut, people_vermont
etc. UPDATE
people_massachusetts
SELECT SET
first_name, hair_colour = ‘brown’
last_name WHERE
FROM first_name = ‘Jane’
people_massachusetts AND
WHERE (or use WHERE NOT to exclude a group) last_name = ‘Doe’
hair_colour = ‘red’
DELETE FROM
AND (or use OR if one of two conditions can be filled)
people_massachusetts
birth_date BETWEEN '2003-01-01' AND '2003-
WHERE
12-31'
address_state = ‘Maine’
ORDER BY (or use GROUP BY to aggregate
duplicates) * means all columns included in the results of your
last_name query
LIMIT % is a wildcard for one or more characters
100
COUNT(), AVG(), SUM(), MIN()/MAX(), IF/ELSE/CASE
SELECT
INSERT INTO [Link],
people_massachusetts (address_city, [Link],
address_state, [Link]
address_zip, hair_colour, age, first_name,
last_name) FROM Orders
INNER JOIN Customers ON [Link] =
[Link]