Data Science
Lesson 15
Data from a Database
OVERVIEW
We will be using Python to create a database file and interact with it. We will use a software called sqlite browser, which you should download and install here: http://sqlitebrowser.org/
CREATE A DATABASE
First, we will create our new database with the code below. Note the import required for interacting with a db file (sqlite3)
In [1]:
import sqlite3
db_name = "users"
connection = sqlite3.connect('{}.db'.format(db_name))
c = connection.cursor()
If you note the files in your current folder, you will see that this code created a new .db file called test.db. If you don't have the db created already, it will create one for you and connect to it. Otherwise, it will just connect. We will discuss how to connect to a remote database later.
NOTE
- For the purposes of this exercise, I've already created a database with users in it for you. However, if you wanted to create a table of your own, the following example code would be helpful:
def create_table():
c.execute("""CREATE TABLE IF NOT EXISTS users
(id TEXT PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT,
gender TEXT)""")```
>where `users` is your table name and `id` is your primary key.
<br/>
<hr/>
<br/>
RETRIEVE ALL THE RECORDS
We'll now grab all the records from the database and observe what they look like so we can interact with them a little bit. Run the following code for all the results.
In [10]:
sql = "SELECT * FROM users;"
c.execute(sql)
result = c.fetchall()
print(result)
We can observe from the output above that all the records have been returned to us, just like we asked. How do we interact with them though? Just like an array!
result[1] # will return ('1', 'Noemi', 'Penwright', 'npenwright0@prweb.com', 'Female')
result[1][1] # will return 'Noemi'
CHALLENGE
What if we wanted to grab a record with a specific id?
In the code block before, write an sql statement that will get the user with id 3.
SCALING UP
Now, this is great and all, but what happens when we have a huge database full of records? We want to be able to only get a few records at a time, work with them, and then request some more (i.e., streaming the data into memory). If we continue trying to work with this large amount of data, our computer won't have enough RAM to support it. Let's see how this is done!
In [17]:
# create an array to hold all of our sql statements so we can execute them all at once.
sql_transaction = []
# we then define a function that will help us build the huge sql transaction to execute all at once.
# in this case, it will execute after it sees 100 sql queries in the array, dump the array and begin
# collecting again.
def transaction_bldr(sql):
# hint: we have to put the global keyword before sql_transaction so the function knows we're
# going to be working with an outer scope variable. Similarly, we can just pass the
# sql_transaction variable to this function through an argument.
global sql_transaction
sql_transaction.append(sql)
if len(sql_transaction) > 100:
# The 'BEING TRANSACTION' execution script tells the connection that you will be starting
# a huge transaction.
c.execute('BEGIN TRANSACTION')
for s in sql_transaction:
try:
c.execute(s)
result = c.fetchone()
print(result[0])
except:
pass
connection.commit()
sql_transaction = []
# loop through all the users and populate sql queries getting only their first name
for i in range(1, 1001):
# we will have to wrap this in a try/except because we pass that error in the transaction_bldr function
try:
sql = "SELECT first_name FROM users WHERE id = '{}'".format(i)
transaction_bldr(sql)
except Exception as e:
print('select error', str(e))
Noemi Scott Torey Hillel Sammy Gregorius Carlene Sean Kerwinn Earl Bruce Dominica Chickie Nancy Alard Emmet Malachi Wendeline Valaria Krishnah Linus Jessa Denys Clarke Danell Ennis Jordan Benedict Mame Lars Leoine Maddy Megan Patricio Conroy Neill Mychal Krysta Isaac Elladine Yelena Amalita Johny Yankee Holly Anna-diane Carmela Freda Darbie Darcee Pooh Josefa Harriott Fayette Albertina Felita Agustin Bathsheba Fernandina Didi Hamid Ellery Torrance Donnajean Asa Lorinda Howey Willow Rena Foster Sholom Vladimir Goraud Justis Ted Beverlie Vernen Guillermo Rouvin Chevalier Conn Ninetta Isabel Bernie Michelle Donnie Marlene Kip Alister Gray Merrili Patsy Alexina Thacher Marilee Holli Ferdinande Luther Tasia Bernette Robinetta Helenka Knox Vonni Erek June Floria Jesselyn Dirk Babette Bethanne Britteny Mathew Danielle Galvin Skye Eddi Yolanda Falkner Temple Felipe Jessalyn Terrel Efrem Ede Melany Randolf Oby Tuesday Alley Clovis Shane Josiah Yorke Horatio Roxane Irma Purcell Stevana Alfonse Lacee Hersh Somerset Boote Granger Taffy Willi Perla Evin Bobbi Camella Adan Lonny Linnea Kaine Samuele Jessa Martina Mavis Fergus Cesaro Shir Rani Teodoro Eduard Nani Major Hester Rowland Jany Leicester Town Berte Freida Franklyn Georgianna Fey Gerek Orren Isiahi Lola De witt Aldous Julian Lucienne Aleen Octavius Alaric Jacky Gerick Delainey Harri Oriana Maryellen Sosanna Kele Dorthea Ellis Lissa Lexi Marlow Arlinda Jaimie Melesa Skelly Moina Tomkin Trula Cchaddie Wilhelmine Thomasa Yvor Evered Mozelle Elsy Marnie Mano Darcie Andros Stillmann Remus Radcliffe Eduard Jacquette Scottie Steve Bill Karrie Rosamond Osgood Guenevere Marillin Car Merry Adair Farrand Carey Welbie Carlye Jody Devinne Jae Flem Bertrand Latisha Cobb Berget Alma Mair Pierette Vanessa Dill Finlay Whittaker Humfried Read Warner Hamid Wendel Bram Conway Jacquelin Gilda Tabb Dory Sonya Mathew Zacharia Claudius Denver Brandon Christian Lombard Rolland Kesley Felicity Sanford Georgy Bonnee Pierce Klara Remus Odette Annalise Josefa Elane Luther Betta Latia Mikol Giselle Gavrielle Corena Mikel Vito Walden Oliy Henryetta Brook Keri Tomi Modestia Kate Shara Edsel Crissie Barbi Harlie Keary Denny Marshall Dion Liva Salvidor Delainey Francene Arleyne Greta Candis Kathy Josi Kaycee Edouard Brendon Nealy Selma Marijo Kimble Mohandis Tann Lennard Nicholas Nerita Wye Colline Carly Christoffer Roderick Mathian Smitty Jessie Megan Julietta Robb Tiffany Claudina Lyndel Agneta Maiga Valle Christyna Muffin Byrann Shaughn Erin Emiline Ranna Bone Smith Herschel Jocelyne Carmencita Ricca Natale Horst Staffard Martina Emalee Gabrila Mauricio Madge Milicent Merna Florie Kesley Joela Wendell Roxy Gennie Thomas Davis Brook Kelila Gasparo Malissia Jerry Bobbe Danika Wrennie Danny Charlene Mikel Imojean Aurlie Chrissy Harlin Araldo Staford Barb Cedric Hedy Jess Nathan Chris Meredeth Daria Wallache Saraann Cherilyn Robbie Doretta Cele Baldwin Maryann Julius Norine Bryce Carin Bonnibelle Kerwinn Rickert Curtice Tessie Zaneta Hartwell Terry Crista Alain Claretta Jabez Adria Clyde Gail Culley Elisha Kristos Stefanie Ted Raf Wilden Ewan Brina Fabian Astrix Isadore Mollie Osbourn Freddi Bobbi Muire Daniel Cull Jacquenette Jeremias Brook Roberta Genevieve Vincenty Juliann Rozalie Leonidas Salomo Liliane Gard Buiron Astrid Lauritz Wain Raimondo Reinaldo Colas Sharyl Fidel Oren Webster Mason Davin Germana Skipp Emmie Edyth Derward Agatha Kristyn Lanny Gabi Mile Vanna Bert Monro Paige Carrol Christean Zedekiah Collen Eal Corine Dennis Siobhan Davis Anna-diane Kary Tory Sukey Jamaal Ragnar Hayward Hansiain Cheri Preston Emylee Jesse Gabie Lanny Corbin Brendan Tania Nester Franciska Genvieve Garvin Jamill Herculie Nap Avery Muffin Sophey Marion Cleo Merrielle Constancia Fiona Malynda Gibb Caron Lyda Charil Nisse Shep Ansell Shanda Jeremiah Felisha Thatch Beryle Zollie Nickey Stacee Prent Casey Mathian Giovanni Peg Marris Charlton Penn Bellanca Rochell Maire Leontyne Petronia Patty Katha Rex Grady Elaine Melvyn Jarrod Daria Tymothy Muffin Burton Kassie Ruy Kelcy Ulric Delinda Stepha Henrik Trace Lorrie Cindelyn Pierre Carole Wren Kane Chan Sterling Derry Lynnet Anestassia Waly Vicky Sebastiano Jozef Georgeanna Silas Daphne Emmy Sal Michele Elmer Violet Berna Gerome Emory Janice Bessy Yehudit Farrell Melisandra Kareem Fair Tyrone Jorgan Lynne Melinde Rakel Gerek Alicea Skippy Guglielmo Alejoa Blinny Chrissy Gail Sibylle Arabele Addison Spenser Shaughn Shelley Brade Staffard Andie Elane Myron Heinrick Sherline Abba Modesta Sidonnie Chaim Johann Rebekah Leilah Lilly Winthrop Nisse Brannon Elmira Emily Helli Sidnee Leanna Clarine Quintilla Blakeley Raviv Anthony Marlowe Berne Carolan Giulio Taylor Mandie Isidor Cherianne Colly Thorpe Lek Osbourne Myles Scottie Jackie Eduino Errol Murial Obed Gar Farrel Micaela Berty Kliment Cece Maurits Benita Cherice Vera Ogdon Sigismondo Farrel Raoul Rania Alvina Marcella Loren Eadith Isidora Annnora Gibby Francklin Emelita Toinette Filide Shermie Kit Ivy Angeli Evangelina Perry Winn Osbourne Lemmie Rubin Brier Cazzie Hailee Deb Selene Farand Rodney Lynnea Daphne Kaja Ric Gwen Claude Janie Magnum Sidoney Malinda Cristal Tait Sebastien Roman Birdie Vite Kory Tobye Jo Rhys Trevar Herschel Durant Willard Jacinthe Vinita Loralie Kendall Car Marjy Leroy Ondrea Brennen Nelle Kassia Rahal Ara Katuscha Doris Miller Vannie Gustie Ninette Smitty Say Roxi Shawn Oswald Kerwin Tudor Salomone Marrilee Christophorus Lucio Roselia Husain Colene Shandie Glennie Sena Dietrich Madella Berny Loraine Dianne Nickolaus Deloria Haven Mason Jerry Rudyard Fanny Faulkner Witty Ivor Llewellyn Vannie Marys Ddene Joyce Dela Sibby Emanuele Eb Judon Archibald Sol Antony Phylis Cornelia Sandra Jessica Katti Max Marys Jayne Wallie Merilyn Keriann Christos Everard Dionisio Lilly Calla Vivyanne Tonya Quentin Barbaraanne Thorin Panchito Hazel Kate Derk Pauli Colet Zondra Nata Wake Stacia Perla Ardis Hedda Blondelle Natka Gordie Gabrila Verena Langsdon Ebeneser Chrysa Dana Gusella Hillary Sally Celia Vinnie Angelo Dav Jay Yard Teodoro Eleen Wait Flory Kalila Massimiliano Alvie Cindy Abby Lou Dannye Shannon Huey Leontyne Roger Mead Adlai Keeley Early Ailey Martainn Dagny Quinton Maurie Eada Neysa Jeni Titus Rudd Tobit Gayla Hobard Glynn Adamo Pam Vinny Alejandra Tiphani Sydelle Nikolaos Jamal Trina Zacharia Paulo Timothee Ronnie Marcy
AWESOME SAUCE
You now know how to pipe streams of data into your memory so you can begin working with it. As you can imagine from the above, a use case for this would be to pause after every 100 transactions, put all the data you need into an array and work with it accordingly. Once you're finished, run the next set of 100 transactions and replace the old results in your array with the new ones!
NOTE
>- **The code to connect to a remote MySQL database is as follows:**
import MySQLdb
# connect to the database
db = MySQLdb.connect("localhost","inmoti6_pytest","pytest","inmoti6_pytest" )
# setup a cursor object using cursor() method
c = db.cursor()
# run an sql question
c.execute("SELECT VERSION()")
# grab one result
data = c.fetchone()
CONGRATULATIONS
You made it to the end of this bootcamp!
Hopefully you were able to learn a bit and become a step closer to becoming a pro Data Scientist.
If you're interested in learning more about Machine Learning and Artificial Intelligence, feel free to contact Amit Maraj (amit.maraj@durhamcollege.ca) for more information!
In [ ]: