Detailed Notes
Focus for this episode: We continue our exploration of index creation and start into multi-keyed indexes.
This tutorial walks through the creation of a database synchronization tool in Python. It continues our Python and SQL Tutorials with a useful application that provides a deeper look at SQL structures and commands.
Repository For Code: git clone [email protected]:rbroadhead/dbsync.git
Transcript Text
[Music] well hello and welcome back we're continuing our season series i guess we're the more series where we're talking about python and sql we're sort of taking the last couple of tutorial series that we've gone through and combining them into an application which is our database sync script uh written in python for mysql last time around we were working on we'd added indexes essentially and foreign keys primary keys things like that as we're going through and we're creating our indexes that don't exist on the destination and we ran into a situation where we had a problem with the names and it threw an exception so what i want to do with this one a little different what we did in the past is we're going to do if you've done anything with it if you thought about it we're going to do the try and accept and we're basically going to say well we're going to keep on going so what we're going to do here that's a python thing so let me put here actually i guess i don't really need to do it let's do it here i'm going to do it i'm going to create my cursor and i'm going to do the execute and then no matter what i'm going to do i'm going to close the cursor anyways so i'm going to go through i'm gonna try to create this thing and what is it complaining about uh it says too broad but i think i'm okay so what i'm gonna do here is i'm gonna say i fail to create index and then i'm going to give it my query idx and i think i'm gonna go with that for now so let's see i'm not gonna print it now unless it's a mistake and we'll just go with that for now so what we're going to do if we run it is get nothing of use let's see did we quit out of this early let's see oh these tables that indexes are not in the target and there were none so everything synced up because we created everything i guess that's the only one last time and we create what happened is we created this first one and for some reason oh let's go look at that uh horse bo index on horses i bet you what we're gonna find somewhere in here [Music] is uh if i go out to that where is that that is on the this guy if i go out to the source for horses and i look at indexes there we go so if we look uh let me go horse and it's not connected right now let's see let's try this reconnect well i think what it was is that we got a we got ult we got secondary indexes on that so if we go to um let's do this uh how do i want to do oh i guess i can do it all from here so if i do uh let's just force a disconnect let's get out of here for a second so if i go to my local and i'm gonna do uh show indexes on what was that horses okay it's not going to like it good now it's going to connect and if i go to tutorial uh actually it's not is it it's describe indexes and we come up here up columns indexes here we go show indexes from not on shoot so let's go look at this uh if we do that and so this is where we made a mistake so here's our indexes now what we want to do uh let's go to local local local local local where is local local ah that's demo there we go the thing that's in bold rob so i want to go in here and i'm going to go to this table and i'm going to make a change because i want to look at how this comes up if we do alter table well i had a little bit of a technical difficulty there so let's just i had to jump out so now i go back and let's look at our show indexes so this is from our source and what we see here is that we actually have um between the two we've got an index so we need this sequence and index which is zero one two three and so we're gonna need to deal with that when we're creating our indexes we need to figure out if it's going to be a multi-column or not and so we need to do let's say so for table and there we're going to show our indexes we're going to go through each index okay so here we have to change this around a little bit so we're going to do column names and let's do we're going to call it a prior prior calm and we're going to do this and so here we're going to say okay if we're in this uh we probably need to do each of these so let's ch we're probably going to change this around a little bit um first we're going to do we're going to say how do we want to do this it's going to be really interesting to do i wonder if we can do it with an order by i don't think we can do an order let's try this order by sequence in index this is probably not going to allow us to do it but let's see what happens yep we can't do that so what we're gonna need to do is we're gonna have to figure out we have to walk through each of these and if it has a two then we're gonna have to go find the other one with that key name and let's say let's see if i can just select key name from whoops this is going to be really interesting so we're going to try this let's see if we can do like this select indexes from horses i'm going to say s source and let's just see if this will work i don't think it'll work uh unknown column indexes oh sorry show indexes yep it's not going to allow us to do it so we're going to have to get a little more tricky with the indexes so we're going to do is we're going to go through each of these and we're going to do um we're going to basically do it like this we're going to have to walk through a little bit differently so let's um let's pull this creation out for now and what we're going to do is we're going to come in here let's say oops we do want that okay so if it's not in the destination names then that's going to be in keys or let's call it let's call this names actually let's call this new names so what we're going to do here is if it's not in there then we're going to do uh new names dot append and we're gonna append the name and let's just get rid of that whoops and um well we'll do this we'll keep it the same but when we go down here i'm going to take this guy and we are going to not exec everything uh actually let's quit yeah let's quit here for now so we create the indexes so now if we do it um and let's go to whoops let's say in the source uh let's see if i can do this let me see if i can get my tool my database a little engineer thing here to work oh i think i just want to do uh drop oops um let's do this connect and execute on local there we go and then i'm gonna do drop index uh horse b underscore oh i need to do on horses i think it's on horses i think how i do my drop there we go so now if i do my show index bill i don't have it so now uh it's not going to execute so i should be able to run it through and uh it's gonna blow up oh because call name is not defined and i gotta go back up here i'm gonna take this little bit partially here and we're going to call it name um we're just going to call it that one for now okay so now it's going to say that it's going to come through and it's going to create because it doesn't exist it's going to try to create it and now what i want to do is say and it gets that in there twice so i can see where he exists twice so what i want to do is i'm going to actually change this a little bit and i'm going to well i'm not going to create that yet so now what i'm going to do this time around is i'm going to say in the new names did i get the new names of pen yeah so i'm going to come out here and i'm going to do print new names let's see how that works and uh okay so there's nothing in new names wait what did i do oh whoops oh i can do that let's do that let's just put it like this we'll print our new names there we go so each one and here we go oh no those are the column names so new names needs to be actually just the name and so now i'm gonna see there we go so if i see the name twice then i'm gonna need to do some let's see so how do i want to do that i probably want to do it as so i'm going to come through here once i get done let's see so i've got my new names and so this is where i'm going to actually build out my stuff so i'm going to do a four name in new names and i'm going to do um let's do this created whoops equals this thing and what i'm going to do with each one is i'm going to say if name in created so that means it already exists else created dot append name and then what i want to do so if the name is in created so if i'm or if it already exists and i need to get rid of this otherwise i'm going to append it and i'm going to do something with it actually what i probably need to do is change this to uh fields so what i need to do yeah i'm gonna have to do this a little differently so okay so this is gonna be um this will be index names and so when i come through here where's index names let's see if i can do this name equals and then i need to get the column i need to get the uh the name of the column and so now what i'm going to do is turn this into a little bit of a dictionary type of an approach and let me make sure i've got that right because i think i don't that's what i thought as i'm going to do that so that's what i want to do okay so my i start with this whoop this and i'm going to do for each of those i'm gonna do i think that's how i want it to do right oh nope i'm okay so i can do it like that dot append name and a pin so okay there we go so that's going to give me my columns that'll work let's see if that worked uh it probably works that way so now um for name in idx names and let's just do this for now let's just do let's get rid of that i'm gonna do print name and see what happens because we are there we go so running some issues here getting this i'm going to because of time constraints i am going to pause this here and we'll come back next time we're going to start digging through this as we are having to rewrite this because what we're going to do is we're going to build out a dictionary of columns or i'm sorry of index names and then with each of those we'll have an array of columns and then we'll use that to build our sql that being said it's time for us to wrap this one up so go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back
we're continuing our season series i
guess we're the more series where we're
talking about
python and sql we're sort of
taking the last couple of tutorial
series that we've gone through
and combining them into an application
which is our database sync script
uh written in python for mysql
last time around we were working on
we'd added
indexes essentially and foreign keys
primary keys things like that
as we're going through and we're
creating our indexes that don't exist on
the destination
and we ran into a situation where
we had a problem with the names
and it threw an exception so what i want
to do with this one a little different
what we did in the past
is we're going to do if you've
done anything with it if you thought
about it
we're going to do the try and accept
and we're basically going to say well
we're going to keep on going
so
what we're going to do here that's a
python thing so let me put
here
actually i guess i don't really need to
do it
let's do it here
i'm going to do it i'm going to create
my cursor
and i'm going to do the execute
and then no matter what i'm going to do
i'm going to close the cursor anyways
so i'm going to go through i'm gonna try
to create this thing
and
what is it complaining about
uh
it says too broad but i think i'm okay
so what i'm gonna do here
is i'm gonna say
i fail to create
index
and then i'm going to give it my query
idx
and
i think i'm gonna go with that for now
so let's see i'm not gonna print it
now unless it's a mistake
and
we'll just go with that for now
so what we're going to do
if we run it
is get nothing of use
let's see did we quit out of this early
let's see
oh
these tables that indexes are not in the
target
and there were none
so everything synced up because we
created everything i guess that's the
only one last time
and we create what happened is we
created this
first one
and for some reason oh
let's go look at that
uh horse bo index on horses
i bet you what we're gonna find
somewhere in here
[Music]
is
uh if i go out to
that where is that that is on the
this guy
if i go out to the source
for horses
and i look at indexes
there we go
so if we look uh let me go horse
and it's not connected right now let's
see let's try this
reconnect
well i think what it was is that we got
a
we got ult we got
secondary indexes on that so if we go to
um
let's do this
uh how do i want to do oh i guess i can
do it all from here so if i do
uh let's just force a disconnect let's
get out of here for a second
so if i go to my local
and i'm gonna do uh show indexes on what
was that horses
okay it's not going to like it good now
it's going to connect
and if i go to tutorial
uh actually it's not is it it's describe
indexes
and we come up here up columns indexes
here we go show indexes from not on
shoot
so let's go look at this uh if we do
that and so this is where we made a
mistake so here's our
indexes
now what we want to do
uh let's go to local local local local
local
where is local local ah that's demo
there we go
the thing that's in bold rob so i want
to go in here and i'm going to go to
this table
and i'm going to make a change because i
want to look at how this comes up if we
do alter table
well i had a little bit of a
technical difficulty there so let's just
i had to jump out so now i go back and
let's look at our show indexes so this
is from our source
and what we see here
is that we actually have
um between the two we've got an index so
we need this sequence and index which is
zero one two three
and so we're gonna need to deal with
that when we're creating our indexes
we need to
figure out if it's going to be a
multi-column or not
and so we need to do
let's say so for table and there we're
going to show our indexes we're going to
go through each index
okay so here
we have to change this around a little
bit so we're going to do
column names
and let's do we're going to call it a
prior
prior calm
and we're going to do this
and so here we're going to say okay if
we're in this
uh we probably need to do each of these
so
let's ch we're probably going to change
this around a little bit
um first we're going to do we're going
to say
how do we want to do this it's going to
be really interesting to do i wonder if
we can do it with an order by
i don't think we can do an order let's
try this order by
sequence in
index
this is probably not going to allow us
to do it but let's see what happens
yep we can't do that
so what we're gonna need to do
is we're gonna have to figure out we
have to walk through each of these
and if it has a two
then we're gonna have to go find the
other one
with that key name
and let's say let's see if i can just
select
key name
from
whoops
this is going to be really
interesting
so we're going to try this let's see if
we can do like this select indexes from
horses
i'm going to say s source and let's just
see if this will work i don't think
it'll work
uh unknown column indexes
oh sorry show indexes
yep it's not going to allow us to do it
so
we're going to have to get a little more
tricky with the indexes
so we're going to do is we're going to
go through each of these and we're going
to do um
we're going to basically do it like this
we're going to have to walk through a
little bit differently so
let's um
let's pull this creation out for now
and what we're going to do is we're
going to come in here let's say
oops we do want that
okay so if it's not in the destination
names
then that's going to be in keys
or let's call it
let's call this names
actually let's call this new names
so what we're going to do here is if
it's not in there
then we're going to do
uh new names
dot append
and we're gonna append the name
and let's just get rid of
that
whoops
and
um
well we'll do this
we'll keep it the same
but when we go down here
i'm going to take this guy and we are
going to
not
exec
everything
uh actually let's quit
yeah
let's quit here for now
so we create the indexes
so now if we do it
um and let's go to
whoops
let's say
in the source
uh let's see if i can do this let me see
if i can get my tool
my database a little engineer thing here
to work
oh i think i just want to do uh drop
oops
um let's do this connect and execute on
local
there we go
and then i'm gonna do drop index
uh
horse b underscore oh
i need to do on horses
i think it's on horses i think how i do
my drop
there we go
so now if i do my show index bill i
don't have it so now
uh it's not going to execute so i should
be able to run it through
and uh it's gonna blow up oh
because call name is not defined and i
gotta go back up here
i'm gonna take this little bit
partially here
and we're going to call it name
um
we're just going to call
it that one
for now
okay so now it's going to say that it's
going to come through and it's going to
create because it doesn't exist it's
going to try to create it
and now what i want to do
is say
and it gets that in there twice
so i can see where he exists twice
so what i want to do is i'm going to
actually change this a little bit
and
i'm going to well i'm not going to
create that yet so now what i'm going to
do this time around is i'm going to say
in the
new names did i get the new names of pen
yeah
so i'm going to come out here and i'm
going to do print
new names
let's see how that works
and
uh
okay so there's nothing in new names
wait what did i do
oh whoops
oh i can do that let's do that
let's just put it like this
we'll print our new names there we go
so each one
and here we go
oh no those are
the column names
so
new names needs to be actually just the
name
and so now i'm gonna see there we go so
if i see the name twice
then i'm gonna need to do some let's see
so how do i want to do that
i probably want to do it as
so i'm going to come through here once i
get done
let's see so i've got my new names and
so this is where i'm going to actually
build out my
stuff
so i'm going to do a
four name
in
new names
and i'm going to do
um let's do this
created
whoops
equals this thing
and what i'm going to do with each one
is i'm going to say
if
name
in
created
so that means it already exists
else
created dot append
name
and then what i want to do
so if the name is in created
so if i'm or if it already exists and i
need to get rid of this otherwise
i'm going to append it and i'm going to
do something with it
actually what i probably need to do
is change this to
uh fields
so what i need to do yeah i'm gonna have
to do this a little differently so okay
so this is gonna be um
this will be
index names
and so when i come through here
where's index names
let's see if i can do this name
equals and then i need to get the column
i need to get the uh
the name of the column
and so now what i'm going to do
is turn this into a little bit of a
dictionary type of an approach
and let me make sure i've got that right
because i think i don't
that's what i thought as i'm going to do
that so
that's what i want to do okay so my i
start with
this
whoop
this
and i'm going to do
for each of those
i'm gonna do
i think that's how i want it to do right
oh nope
i'm okay
so i can do it like that
dot append
name and a pin so
okay
there we go so that's going to give me
my columns that'll work
let's see if that worked uh it probably
works that way
so now
um for name in
idx names
and let's just do this for now let's
just do
let's get rid of that i'm gonna do print
name
and see what happens
because we are
there we go
so running some issues here getting this
i'm going to because of time constraints
i am going to
pause this here
and we'll come back next time we're
going to start digging through this as
we are having to rewrite this because
what we're going to do is we're going to
build out a dictionary of columns or i'm
sorry of index names and then with each
of those we'll have an array of columns
and then we'll use that to build our sql
that being said it's time for us to wrap
this one up so go out there and have
yourself a great day a great week and we
will talk to you
next time
you