Detailed Notes
Databases are a part of almost every modern application. Thus, we often need to use SQL to create tables and a database that houses them. This example shows how to call SQL from Python to build your database and a table. Check out our Python and SQL series for more in depth examples and source code.
You can find out more through our online classes at https://school.develpreneur.com and register for free. Registration will add you to our email list and you will periodically receive coupons for courses as well as notifications of the latest releases.
Transcript Text
foreign [Music] well welcome back we are going to continue looking at we're in a an application a project and we're looking at some of the questions we need to answer whether you do that as a standalone or you're doing it as part of this ongoing application so right now what we want to do is we're going to focus on how to create a database in a table in Mariah my sequel or also Maria DB with python now briefly I do want to mention that MySQL has effectively become Maria DB if you deal with it from the the non-commercial the free side if you go download it you're going to see now that if you go to the MySQL site you're going to be downloading mariadb there are some changes in it and so that can cause a little bit of issue with your with your connection and things like that depending on what your driver is but it should be pretty much a one-to-one change and you're off and running whatever you see documented for MySQL unless it's very old should hold true for Maria DB now with python what we have to do and we're going to have I've got some code pre-written this time just because it's sort of a pain it's going to import the MySQL and the MySQL connector now I know so in order to do that it is not going to be so like I use pip3 you're not going to do pip 3 install MySQL which you would normally do you also should spell that with two L's it's actually going to be my SQL connector and that you have to spell correctly also and once you do it in this case it's going to show you hey you've already done it and uh yeah requirements already satisfied once you have that then what you can do and what we're doing here just to sort of a recap I guess we've got a class I'm just going to have a class called my database and I'm going to have all of the database related stuff sitting in that so that when I run the we'll call this these the script call that well and we'll check those thing here to test it at the end so we're going to have this thing called my database so we're going to create this class it's going to have a database name this is going to be some database we're going to connect to because MySQL wants you to connect to something the one that's almost always going to well is always going to exist is going to call is called MySQL even if it's mariadb and it's really it's the admin type of table so depending on how you connect you may or may not have some issues with it the key is really just to connect because then after that we should be in a database and actually more specifically you can always overwrite this and do it to a database you already have rights to permissions to and we're going to have a connection as part of our class and so here's the first real meet up here is just a query that we're going to talk about later so we're going to start with our connect it's it's actually fairly straightforward you are going to send a dictionary of configuration information which is your user your password the hostname the database you're connecting to which we're pulling from up here and then this Ray Zone warnings because it is something that I found particularly with Maria DB you need to have that there otherwise it will cause issues and not let you to connect and once you do that you're going to use the MySQL dot connector which is up here and then you're going to connect and you're going to give it that config information so it's it's fairly straightforward what I've got here is going to be useless to you probably so you're going to give it whatever your username is whatever your password is probably in this case if it's local 127001 if not give it the IP address and whatever the database name is so if you do that if you write that code right here we're going to say here and we're going to connect so let's just do this we're going to say we'll go ahead and wrap this whole thing try accept and let's do this so return value is going to be either negative one or one then we're going to return the return value whoops and so here let's just say I'm not going to do this yet I am going to close it afterwards and I'm going to do let's just do this print that so I can see if I connect or not and if I run it then what I'm going to see here is I got a one so I have connected to the database pretty simple pretty straightforward so that's our first thing is we want to be able to connect to the database once we've connected the database now let's actually create a database that was basically connecting to the server so we're going to create a database in within that the way you do that within MySQL is actually really simple it's create space database space the name and so we want to do here is we're going to come down here and well actually I'm sorry first let's go look at this let's actually do something in this so is that list tables that is oh this databases so let's do this list databases was just right here is very simple command it's just show databases once you're in there execute show databases so what we're going to do and this is sort of our first shot is we're going to come in we're going to when we connect we already took that connection and held it within the class so that cnx is now there so we come in we do this list databases we're going to get a cursor off of our connection so we've got our connection in there we haven't closed or anything then we've got this query show databases and we're going to execute use that cursor to execute and then now that we've executed it we're going to fetch all of the rows back and we're going to store that in this thing called rows and we're just going to walk through and say four row in rows print the row return value equals one otherwise it's going to be negative 1. so that will give us here we don't need a database for list databases and then we're going to close the connection we're done so now if we look at it if we get this is that we come in and we get the one for the connection and these are all the databases that I happen to have in this one somewhere in here you will see MySQL but you also see that each of these is a uh pairing within itself so what I can do is if I go into my list tables what I can do is I can try to do the first piece and get just the name let's see how that works out for us and that gives us just the names for everything so did I do that right oh that's those tables sorry wrong one that's why I shouldn't be in let's see I want list databases here if I do row zero now I'm getting all of the database names and this is really just a you know convenience kind of utility function but to show that now we have actually connected to the database and we have we're able to do something with it so now that we can list databases we're going to do an I mean mydb Dot create database and we're going to give it a name and so this one is going to be actually oh create database so let's call this create database and we're going to call this Shorty this is going to be our database and so now let's move our database list afterwards and so if we create this database which is right here create database is very straightforward we're going to do a create it's CreateSpace database and then the name we're going to give it the name we're going to actually go ahead and set the name that we're working with now that doesn't set it within the database itself or within the server itself so what we would really want to do is come in here and after we set our name we're just going to do a use and that'll switch our database over we're going to execute the query and we can go ahead and commit and that will put us so that we're actually seeing the the database that we're in so what we're going to do let's do this we're going to come in this time we're going to connect and we're going to do a list tables and we'll show that later I'll talk I'll walk through that a little bit but right now it's just to show it so when we first come in we're going to connect to mySQL and I think it's going to give us a list because I'm I think I've got a user that has that permission we're going to find out and then we're going to create shorty we're going to list the databases and see if it's there and then we're actually going to list the tables in shorty because we should be in that database so in doing that and let's do this let's do this tables whoops I think skills are a little bit off okay so we'll do that that gives us something to work with now there's some other things we could do we might want to put it up in those functions but for now we're just going to keep it simple so if we run it now let's go look at this what we're going to see is here we go so we create it we come in and we list tables and we see initially these are all MySQL tables and you can sort of see stuff because it's like the servers and stuff like that then we come in and we're going to list our databases and boom because we've already created shorty it's right there so we have that database now and then we list tables we're getting nothing on that because there are no tables found because we haven't created any and shorty so now let's just do the same thing and let's get the list databases and what's tables we will get those out of the way so let's see what happens if we try to create the database again can't create it it already exists so what we can do with this yes I can't believe we can do if not exists and there's better ways to do this but we're going to do if not exist name I think that will work we're gonna find out uh so okay uh oh that needs to be not exist that needs to be double quotes the single quotes need to wrap our name which is why there are better ways to do this which we will talk about in a minute and then it's there so it's not actually that I forget what it actually is called so we're just going to leave it at this and we're just not going to worry about it again so we're going to do this try catch it's going to print that it's going to do return value and actually what we can do here that is the create database we're not even going to bother printing it right now we'll just return it so it doesn't really matter and we'll carry on so now we're going to get no errors closely because we're not displaying them and oh we don't need to print this we do always need to connect we're always going to need to set this up so we're going to King to connect we don't need to create the data set base anymore so now what we're going to do is the database name is now going to be shorty so we're going to get that from the start so we're going to connect right away to shorty and so if we do mydb dot list tables we should see none from the start and there you go we don't see any so now we're connecting to our database we don't need to create it anymore so we'll just put this up here somewhere and we'll put that there okay uh wait no oh yeah we'll do that so we've got all that now what we need to do and this is sort of our cleanup work of resources so now it's really not very useful if you don't have any tables so let's finish that question which was the original one here is hey how do I create well that's in the way how do I create a database and a table within python so we're going to do the same thing and there are a couple ways you can do it but we're going to do it oops in here because it's going to be something could be useful in the long run for us to like build a database from scratch so we're going to use this create table SQL this is the what we're going to execute this time around and the way this is going to do it is it's going to be we've created this out so it's going to be table links and there's a lot of way you could build this on the fly if you really wanted but we're not going to in this case it's going to be the table's name is going to be links we're going to have an ID because we need one and it's going to Auto increment so this means the first record will be one the second one will be two three four Etc we're going to have the original link that was sent in because this is the links that are dealing with this Shorty application which basically what it cares about is the Source here if you retrieve new UL there's the it's whatever your old URL is and then what is that new URL what is that ID to give you the new URL and so in here we're going to store the old one we're going to have a code just in case which is usually going to be probably we're going to a couple ways we do it for now we're going to start what's going to be the ID and then um or maybe not we'll figure out what we want to do with that then the user ID and we we're just going to assume that at some point we may allow for a user ID if not it's going to just be a zero so I have to worry about it and then of course what we need to do we're not going to go too deep into it but then we need a primary key which is going to be our unique ID that we're generating because we are Auto incrementing so in order to do that when we create table what we do here oh we don't need name create table is going to come in it's going to execute within that class the self dot create table SQL it's going to commit and then we're off and running and so now if we do we're going to create table whoops then we are going to see now let's do this it's going to be the tables in and we're going to go ahead and do my DB dot DB name so now we're going to see where these things at if we run it boom tables and shorty is links and what we want to do since we have that list tables let's fix that as well so here and do the same thing so we're just going to take that first piece we get the name and not that full structure now if we run it we're going to have a problem because it's going to say the links already exists and so we could check that but right now we're just going to go ahead and let that allow that to go it's going to say hey tables and Shores links and so here we go so we have create a database and a table in Python using a in MySQL using that connector so check question answered we're going to continue on and go into our next question next time around so just take a look out there if there's anything else that's a side note or something you're like Hey how do they get there check it out because we probably have one of those out there as we are building out this this approach to information to build out your applications but we're going to continue back with this application as well and we're going to come right back into a couple things we're going to actually save a record and then we're going to be able to search the table for a record because we're going to start linking our database into our little shorty functionality here thanks a lot for your time we'll catch you next time hello this is Rob with developmentor also known as building better developers wanted to announce that we have school.developmentor.com feel free to check it out if you like any of this information any of the content that we've sent and you would like to see more you can come out you can enroll for free we have free courses we've got places for you to get better at just learning a technology our how to's you can work on your business skills we can help you with becoming a better developer as encoding and things like that a lot of the stuff you've seen on YouTube we also have out at school.development or we just have it a little more of a educational format and a way for you to track your progress as you move forward becoming a better developer thank you
Transcript Segments
foreign
[Music]
well welcome back we are going to
continue looking at we're in a an
application a project and we're looking
at some of the questions we need to
answer whether you do that as a
standalone or you're doing it as part of
this ongoing application
so right now what we want to do is we're
going to focus on how to create a
database in a table in Mariah my sequel
or also Maria DB with python now briefly
I do want to mention that MySQL has
effectively become Maria DB if you deal
with it from the the non-commercial the
free side if you go download it you're
going to see now that if you go to the
MySQL site you're going to be
downloading mariadb there are some
changes in it and so that can cause a
little bit of issue with your with your
connection and things like that
depending on what your driver is but
it should be pretty much a one-to-one
change and you're off and running
whatever you see documented for MySQL
unless it's very old should hold true
for Maria DB now with python what we
have to do and we're going to have I've
got some code pre-written this time just
because it's sort of a pain it's going
to import the MySQL and the MySQL
connector now I know so in order to do
that
it is not going to be so like I use pip3
you're not going to do pip 3 install
MySQL which you would normally do you
also should spell that with two L's it's
actually going to be my SQL connector
and that you have to spell correctly
also
and once you do it in this case it's
going to show you hey you've already
done it
and uh yeah requirements already
satisfied once you have that then what
you can do and what we're doing here
just to sort of a recap I guess we've
got a class I'm just going to have a
class called my database and I'm going
to have all of the database related
stuff sitting in that so that when I run
the we'll call this
these the script
call that
well and we'll check those thing here to
test it at the end so we're going to
have this thing called my database so
we're going to create this class it's
going to have a database name this is
going to be some database we're going to
connect to because MySQL wants you to
connect to something the one that's
almost always going to well is always
going to exist is going to call is
called MySQL even if it's mariadb and
it's really it's the admin type of table
so depending on how you connect you may
or may not have some issues with it the
key is really just to connect because
then after that we should be in a
database and actually more specifically
you can always overwrite this and do it
to a database you already have rights to
permissions to
and we're going to have a connection as
part of our class
and so here's the first real meet
up here is just a query that we're going
to talk about later
so we're going to start with our connect
it's it's actually fairly
straightforward
you are going to send a dictionary of
configuration information which is your
user your password the hostname the
database you're connecting to which
we're pulling from up here
and then this Ray Zone warnings because
it is something that I found
particularly with Maria DB you need to
have that there otherwise it will cause
issues and not let you to connect and
once you do that
you're going to use the MySQL dot
connector which is up here and then
you're going to connect and you're going
to give it that config information so
it's it's fairly straightforward what
I've got here is going to be useless to
you probably so you're going to give it
whatever your username is whatever your
password is probably in this case if
it's local
127001 if not give it the IP address
and whatever the database name is
so if you do that
if you write that code right here we're
going to say here and we're going to
connect so let's just do this we're
going to say
we'll go ahead and wrap this whole thing
try accept
and let's do this so return value is
going to be either
negative one
or one
then we're going to return the return
value
whoops
and so here let's just say I'm not going
to do this yet
I am going to close it afterwards
and I'm going to do let's just do this
that so I can see if I connect or not
and if I run it
then what I'm going to see here is I got
a one so I have connected to the
database pretty simple pretty
straightforward
so that's our first thing is we want to
be able to connect to the database once
we've connected the database now let's
actually create a database that was
basically connecting to the server so
we're going to create a database in
within that the way you do that within
MySQL is actually really simple it's
create space database space the name
and so we want to do here is we're going
to come down here and well actually I'm
sorry first let's go look at this let's
actually do something in this so is that
list tables that is oh this databases so
let's do this list databases was just
right here
is very simple command it's just show
databases once you're in there execute
show databases so what we're going to do
and this is sort of our first shot is
we're going to come in we're going to
when we connect
we already took that connection and held
it within the class so that cnx is now
there so we come in
we do this list databases we're going to
get a cursor off of our connection so
we've got our connection in there we
haven't closed or anything
then we've got this query show databases
and we're going to execute use that
cursor to execute
and then now that we've executed it
we're going to fetch all of the rows
back and we're going to store that in
this thing called rows
and we're just going to walk through and
say four row in rows print the row
return value equals one otherwise it's
going to be negative 1.
so that will give us here we don't need
a database for list databases and then
we're going to close the connection
we're done so now if we look at it
if we get this is that we come in and we
get the one for the connection
and these are all the databases that I
happen to have in this one somewhere in
here you will see MySQL but you also see
that each of these is a
uh pairing within itself so what I can
do is if I go into my list tables what I
can do is I can try to do the first
piece and get just the name
let's see how that works out for us
and that gives us
just the names for everything so did I
do that right
oh that's those tables sorry wrong one
that's why I shouldn't be in
let's see
I want list databases
here if I do row zero
now I'm getting all of the database
names and this is really just
a you know convenience kind of utility
function but to show that now we have
actually connected to the database and
we have we're able to do something with
it so now that we can list databases
we're going to do an I mean mydb Dot
create database and we're going to give
it a name and so this one is going to be
actually
oh create database so let's call this
create
database and we're going to call this
Shorty
this is going to be our database
and so now
let's move our database list afterwards
and so if we create this database which
is right
here create database is very
straightforward we're going to do a
create it's CreateSpace database and
then the name we're going to give it the
name we're going to actually go ahead
and set the name that we're working with
now that doesn't set it within the
database itself or within the server
itself so what we would really want to
do is come in here
and after we set our name we're just
going to do
a use and that'll switch our database
over we're going to execute the query
and we can go ahead and commit and that
will put us so that we're actually
seeing the the database that we're in so
what we're going to do let's do this
we're going to come in
this time we're going to connect
and we're going to do a list tables
and we'll show that later I'll talk I'll
walk through that a little bit but right
now it's just to show it so when we
first come in we're going to connect to
mySQL and I think it's going to give us
a list because I'm I think I've got a
user that has that permission we're
going to find out
and then we're going to create shorty
we're going to list the databases and
see if it's there and then we're
actually going to list the tables in
shorty because we should be in that
database
so in doing that and let's do this let's
do
this
tables whoops
I think skills are a little bit off
okay so we'll do that that gives us
something to work with now there's some
other things we could do we might want
to put it up in those functions but for
now we're just going to keep it simple
so if we run it now
let's go look at this what we're going
to see is
here we go so we create it we come in
and we list tables and we see initially
these are all MySQL tables and you can
sort of see stuff because it's like the
servers and stuff like that
then we come in and we're going to list
our databases and boom because we've
already created shorty
it's right there so we have that
database now
and then we list tables we're getting
nothing on that because there are no
tables found because we haven't created
any and shorty
so now let's just do the same thing and
let's get the list databases and what's
tables we will
get those out of the way so let's see
what happens if we try to create the
database again
can't create it it already exists so
what we can do with this
yes I can't believe we can do if not
exists
and there's better ways to do this but
we're going to do if not exist name I
think that will work we're gonna find
out
uh
so okay
uh oh that needs to be
not exist
that needs to be double quotes
the single quotes need to wrap our name
which is why there are better ways to do
this which we will talk about in a
minute and then it's there so it's not
actually that I forget what it actually
is called
so we're just going to leave it at this
and we're just not going to worry about
it again so we're going to do this try
catch it's going to print that it's
going to do return value and actually
what we can do here
that is the create database we're not
even going to bother printing it right
now we'll just return it so it doesn't
really matter
and we'll carry on so now we're going to
get no errors
closely because we're not displaying
them and oh we don't need to print this
we do always need to connect we're
always going to need to set this up so
we're going to King to connect we don't
need to create the data set base anymore
so now what we're going to do
is the database name
is now going to be shorty so we're going
to get that from the start so we're
going to connect right away
to shorty and so if we do mydb dot list
tables
we should see none from the start and
there you go we don't see any
so now we're connecting to our database
we don't need to create it anymore
so we'll just put this up here somewhere
and we'll put that there okay
uh wait no oh yeah we'll do that so
we've got all that now what we need to
do and this is sort of our cleanup work
of resources
so now it's really not very useful if
you don't have any tables so let's
finish that question which was the
original one here is hey how do I create
well that's in the way how do I create a
database and a table within python so
we're going to do the same thing and
there are a couple ways you can do it
but we're going to do it oops
in here because it's going to be
something could be useful in the long
run for us to like build a database from
scratch
so we're going to use this create table
SQL this is the what we're going to
execute this time around and the way
this is going to do it is it's going to
be we've created this out so it's going
to be table links
and there's a lot of way you could build
this on the fly if you really wanted but
we're not going to
in this case it's going to be the
table's name is going to be links
we're going to have an ID because we
need one and it's going to Auto
increment so this means the first record
will be one the second one will be two
three four Etc
we're going to have the original link
that was sent in because this is the
links that are dealing with this Shorty
application which basically what it
cares about is the Source here if you
retrieve new UL there's the it's
whatever your old URL is and then what
is that new URL what is that ID to give
you the new URL and so in here
we're going to store the old one
we're going to have a code just in case
which is usually going to be probably
we're going to a couple ways we do it
for now we're going to start what's
going to be the ID
and then
um
or maybe not we'll figure out what we
want to do with that then the user ID
and we we're just going to assume that
at some point we may allow for a user ID
if not it's going to just be a zero so I
have to worry about it and then of
course what we need to do we're not
going to go too deep into it but then we
need a primary key which is going to be
our unique ID that we're generating
because we are Auto incrementing so in
order to do that when we create table
what we do here oh we don't need name
create table is going to come in it's
going to execute
within that class the self dot create
table SQL it's going to commit
and then we're off and running and so
now if we do
we're going to create table
whoops
then we are going to see now let's do
this
it's going to be the tables in
and we're going to go ahead and do my DB
dot DB name so now we're going to see
where these things at if we run it boom
tables and shorty is links and what we
want to do since we have that list
tables let's fix that as well
so here and do the same thing so we're
just going to take that first piece we
get the name and not that full structure
now if we run it we're going to have a
problem because it's going to say the
links already exists
and so we could check that but right now
we're just going to go ahead and let
that allow that to go it's going to say
hey tables and Shores links and so here
we go so we have
create a database and a table in Python
using a in MySQL using that connector so
check question answered we're going to
continue on and go into our next
question next time around so just take a
look out there if there's anything else
that's a side note or something you're
like Hey how do they get there check it
out because we probably have one of
those out there as we are building out
this this approach to information to
build out your applications but we're
going to continue back with this
application as well and we're going to
come right back into a couple things
we're going to actually save a record
and then we're going to be able to
search the table for a record because
we're going to start linking our
database into our little shorty
functionality here thanks a lot for your
time we'll catch you next time
hello this is Rob with developmentor
also known as building better developers
wanted to announce that we have
school.developmentor.com feel free to
check it out if you like any of this
information any of the content that
we've sent and you would like to see
more you can come out you can enroll for
free we have free courses we've got
places for you to get better at just
learning a technology our how to's you
can work on your business skills we can
help you with becoming a better
developer as encoding and things like
that a lot of the stuff you've seen on
YouTube we also have out at
school.development or we just have it a
little more of a educational format and
a way for you to track your progress as
you move forward becoming a better
developer
thank you