query

Python MySQL Tutorial - Setup & Basic Queries (w/ MySQL Connector)



Sharing buttons:

hello everyone and welcome back to a

brand new tutorial series on MySQL SQL

and Python now the goal of this series

is to get you familiar with SQL which is

a structured query language that is used

in all different kinds of databases and

then to apply that knowledge to MySQL

which is a popular database that's used

by a lot of companies and a lot of web

applications and things of that nature

so essentially before I go too far I

just want to tell you why you might

actually want to learn this especially

if you're just kind of clicking to see

what this is MySQL is usually actually

required or a MySQL but SQL is usually a

required skill for most programming jobs

even entry-level positions so recently

I've actually been looking at a few

different positions for programming and

almost all of them require that you know

SQL or you have some kind of experience

with relational database systems like

SQLite MySQL and the other ones that

exist out there so anyways if you're

looking for a job I'd recommend that you

guys follow along with this tutorial or

at least learn SQL on your own time

because it is something that you may be

tested on and be expected to know or be

familiar with for a certain position so

anyways with that being said let's get

started what we're gonna do in this

video is be downloading the tools that

we need to actually work with MySQL now

an important thing about MySQL is that

it's different from some other database

systems like SQLite and this typically

is designed to run on a server so we're

gonna be doing this on our local machine

for this specific video and in later

videos we'll actually deploy this out to

a server and see how to work with it

there but just so you know this

typically runs on its own computer its

own machine typically a Linux machine

and what happens is a client can connect

to that server and use the database like

that whereas a database like SQLite just

runs locally on whatever it's using so

this is typically in my opinion more

powerful and more used in terms of like

large applications you typically have a

dedicated server for your database but

anyways let's go ahead and get started

and download things we need to so we're

gonna head over to this web link right

here this should be able to bring it

bring you guys right to it'll leave it

in the description now if at any point

you're asked to make an account you

don't need to do that you can go through

this without making one just so you're

aware of that

so anyways once we get to this page the

first thing we're gonna

load is this installer right here so

there's two options you can choose

either one you want I've downloaded the

larger one because that means I don't

have to wait for this web installer to

install the rest of it once it's

downloaded and once we have that

downloaded we're gonna run through the

installer which I'll do right now

ok so I've opened up the Installer here

for my downloads folder and now it's

bringing me to a page that looks like

this now here you can choose which one

you want from this list now I'm gonna

recommend we just go developer default

we need at least the server and we need

some of the client stuff as well because

well we're gonna be connecting to it

from this specific machine but developer

deep default is fine so we'll click Next

let me go here and this is MySQL for

Excel we don't need that unless I mean

you want that so I'm just gonna click

yes ok that's fine and now we have all

these things that need to be installed

so let's go ahead and click execute now

if for some reason any of these don't

work you can always reinstall things

after I'm with like a previous version

of MySQL anything like that so don't be

worried if these fail for me the first

time doing it some of them did fail and

you can go back and reinstall them and

I'll show you how to do that so once

this is done I'll be right back and

we'll continue alright so we've gone

through here and noticed that this

connector for python failed now we do

need to install this but I'm gonna do

this a different way so don't worry

about that and let's go ahead and

continue for now this next button works

ok so now we're here and it says do you

want an eye and a node database cluster

or standalone MySQL server again this is

not super important we're just gonna go

standalone server as the default here

now we're gonna have this that comes up

we can just leave this alone

we don't need to touch this for now

because this is just gonna be on our

development computer as noted here you

can see that if you're gonna run this on

a server you could choose server

computer dedicated computer whatever it

is so anyways let's go ahead and

continue here now it's gonna ask for all

this let's just go the recommended use

strong password encryption we'll click

Next and now it's gonna ask us for a

root password now it's important that

you remember this password so make it

something you're gonna remember now

there we go I'm gonna type mine and I've

just made mine root so that I remember

it you guys can make it whatever you

want and now we need to create a user

account so I'm gonna start by doing this

I'm gonna make the username Tim for host

will leave like that and for password

I'm gonna make the

Timm as well uh what does the password

does not mean okay so I'll make it reach

it's the password all right there we go

and continue on now we have a database

user we have our root password and we

can continue okay so now this is where

we might want to look at some things

here so what this is saying is this is

gonna run this as a Windows service

which essentially means it's gonna run

in the background continuously now I

want this to happen because I'm gonna be

working with MySQL but some of you may

not want this so just keep that in mind

when it says you know configure MySQL as

a Windows service you don't need to do

this but it's gonna be easier in the

future if you just leave it running in

the background now for the service name

you can change this if you want I'm just

gonna leave this the same and move next

all right so now we have these that we

need to do so let's just click execute

wait for that to finish up and then

we'll be good to go okay so that's

finished so I'm gonna go ahead and click

finish here and then we'll go through

and click Next it's gonna say bootstrap

MySQL router we don't need to do that so

we'll just hit finish okay now we'll go

next again and now it's gonna ask us for

this one more time so the password for

root is gonna be root so we'll do that

when it says show my SQL server instance

that may be running in read-only mode we

don't need to do that I will check that

that's works correctly with the password

for root which my password was root and

then we'll hit next

okay now finally we'll click execute

we'll go through this and the server

should hopefully didn't be installed on

our machine we have a few more steps to

go through and then we'll actually be

ready to start writing some code here

okay so next thing here we're gonna hit

next again and so start MySQL workbench

after setup start shell after setup I

don't actually need either of these so

I'm gonna click finish but you can

launch these if you want okay so now we

have successfully installed MySQL now if

we want to actually like kind of mess

with this and see what it is I mean we

can type MySQL first of all there's a

MySQL installer which means we can

install other things that we need to and

we have a workbench and a shell so let's

open up the workbench and just kind of

explore what this is we're not really

gonna use it but I just want to show you

that we do have a tool that's able to

kind of navigate our database so we can

see we have one connection here

I gotta type in my password so let's

type in root and there we go so we had

one instance there now I'm inside here

we this is where we can actually type

SQL queries directly and then we can

kind of mess with the database look at

it see all this stuff that's going on in

here I'm not really gonna explain this

but I mean if you get more advanced than

you can mess with this graphical

interface we're not gonna deal with that

for now though okay so now we need to

install the connector for Python know

what the connector allows us to do is

actually make SQL queries from Python

code which is kind of the whole point of

this tutorial series all right so some

of you guys might have seen that when

you installed that your Python connector

failed now if it worked properly chances

are that you don't need to do this next

step so I'm gonna show you how to check

so what we're gonna do is just run a

Python command inside of like our

command prompt that's what I'm gonna do

at least you guys can do this from like

ideally like wherever you write your

Python code is where you're gonna do

this anyways what I'm gonna do is I'm

just activating my base directory

because this is anaconda that I'm using

it's not really relevant just gonna type

Python you can see that my Python

version is three point seven point two

it shows right there that's how you kind

of check if you're in the right version

and then what I'm gonna do is just try

to import my SQL now if that import

works correctly and there's no error

then you have everything installed and

you don't need to do anything else

but if for some reason it fails we need

to type in another command now what

we're gonna do is we can do this in two

ways for this MySQL Python connector we

can download it from the download link

that I have in the description and you

can pick which version of Python it is

that you need so in this case I'd like

three seven two seven three five all of

that so you can download the correct one

and run through that installer and it

should install it for you or what you

can do is do a pimp command which will

install this now I'm gonna use the pimp

command and show you how this works it's

pretty much just pip install and then in

this case we go MySQL like that -

connected now wait for this to run and

once this runs and installs you're good

to go and we actually start writing the

SQL queries so again just to recap here

you're gonna test to see if you can

import MySQL if you can do that properly

in Python wherever you're writing the

Python code you're good to go to this

next step otherwise you need to run pip

install MySQL connector or download the

file from the link

the description run through that

installer and it should install that for

you

all right so now that we've installed

everything what we're gonna do is test

to make sure that everything's working

now I'm gonna provide a few solutions if

some stuff doesn't work so don't freak

out immediately if something's not going

correctly now what we're gonna start by

doing is importing MySQL connector now

just do this in any new Python file so

I'm just doing this in sublight X it

doesn't really matter just run this in

any Python file you want and make sure

that this command works we've already

tested this but just you know run this

make sure you don't get an error next

what we're going to do is try to connect

to our database so what I'm gonna do is

say DB equals MySQL dot connector dot

connect now before I even bother typing

the rest of this stuff what I want you

to do is run this so I'm running this by

just hitting ctrl B in sublime text and

make sure you don't have any errors with

this because if you have an error we

need to fix that so if you run this and

you get something saying like there's no

attribute connect don't freak out what

you need to do is go to the website and

install that connector file that I

talked about before so I think I have it

actually I don't still have the window

open otherwise I would show you guys but

essentially what I showed before when I

said you know this is the Python

connector download for whatever version

of Python you have you need to install

that and installing that should

hopefully fix your problem now that's

because sometimes the pimp command

doesn't work properly when you do that

pip install MySQL connector whatever it

is so just run the actual file and

hopefully that should work and be proper

for you guys okay so now that this

command is working what we need to do is

actually connect to our database now

remember I was saying before what you

typically end up doing is you're gonna

connect to an actual server so different

computer all alone now since this is

running on the same computer as the one

that we're actually writing this code on

what we're gonna type here for the host

attribute is the localhost so I'm gonna

say don't connect host localhost next

I'm gonna put the user now again this

user can be whatever you want I'm gonna

put it as root but you could make it

whatever user you created so in that

case I made a user called Tim I could

connect with that but let's just do root

and then for passwd which is just your

password I'm gonna type root as well now

that was what I select

as my password you guys might have a

different one that you're gonna put this

just in plain text whatever the password

was now let's run this again and see if

that's working okay

that's working we're good we can move to

the next step so the next thing that

we're gonna do is create a cursor object

using this database and execute one SQL

query to create a new database so what

I'm gonna do is I'm gonna say my cursor

and you I mean you can call us whatever

you want equals DB cursor like that now

that we have this what we can do is kind

of curse through the database and run

SQL queries and get information that we

want so the first query I'm gonna make

is gonna be create a new database now if

you're not familiar with SQL queries

don't worry we're gonna go into detail

but exactly how they work but the way

that this one works is we do my cursor

dot execute and inside here we're gonna

put a string which is going to be the

query that we want to execute so this is

kind of the standard way to do this you

say my cursor dot execute and then your

query goes inside of a string so in this

case what I'm gonna do is I'm gonna say

create database and this isn't all

capitals and I'm gonna call this one

test database like that

now notice sub-line text is noticing

that this is a query so it's

highlighting it for me but your editor

might not do that so don't worry anyways

we have that so what I'm gonna do now is

go ahead and hit control B we see

there's no issues everything worked and

now we've officially created a new

database now what I'm gonna do is change

my connection option here at the top

when I connect to connect to this

specific database that I just created so

now what I'm gonna do is put data base

here is equal to and we're gonna put the

string test data base because that's

when we already made write so we'll do

that here test data base and then we can

actually get rid of this execute command

like that we can run this and we can see

that we don't get any errors so I know I

haven't showed you guys much we've just

got everything set up but hopefully

everything's working here if you have

questions or concerns leave a comment in

the next video we'll get into actually

working with the database committing

some stuff adding some things

understanding how these queries work and

all of that but as always if you guys

enjoyed make sure you leave a like and

let me know what else you want to see

from the series in the comments down

below