Detailed Notes
Focus for this episode: Create simple Python script Connect to a database Retrieve and display information
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 i am into a slight veering from the sql uh tutorials and things like that and want to get into something that's a little more complicated but we'll still be dealing with databases and and how we can use them and the programming and things like that we're gonna focus uh for this next series of stuff and i have no idea how long this is gonna take because i'm gonna build out an application this is something that's been a an itch i've wanted to scratch for a while years and it's really it's the idea of a database synchronizer of some sort uh you can always do backups and you know restores and things like that but i want to do something that is a little bit more um more about the structure much like some of the tools that are out there like for example if we've looked at our django stuff they have got models and you've got a way to do migrations and basically track your database there and there's some other things i've used over the years that have similar tools one that's uh an old library called torque actually was an excellent one you would define your database in xml and then it would it could generate classes around that it's something that's a little more uh targeted and the goal here is to build a this can be a python application and you'll see in the notes i'm going to be using jetbrains uh their product called pycharm but you can also use you can use pretty much whatever you want if you want to use like a visual studio code it's got some pretty good python plugins and there are definitely other ids out there this just happens to be the one i'm going to be using and our goal is going to be to create this application that essentially you can point it at one database and it's going to look at differences between that and a database on another server so you know whether there are missing tables missing fields field types indexes you know we're trying to get to all of it and we'll see how far we can go with this because i haven't done it before i've played around with some of these concepts and uh you know and this is not like you would do in a you know in a django or some of those other tools i mentioned where you build it from scratch using this or some of them do have some sort of a reverse engineer but you know essentially where you build your database you have some way that you model it outside of the database itself for example you know through xml files or something like that and then you use those to generate or update or migrate your database instead this is going to be point this thing at database a point it at database b and let's see if those things exist in both and so this is going to be a programming experiment experiment as well as some of the other things that we're going to get into and we're going to be getting into um for example we're going to get into you know pulling describing tables and generating basically dynamic sql and some things along those lines so let's start with uh in this first little part there is a new project thing that pycharm will do and so i went ahead and did that and it took a little bit because i you know threw in some libraries and things like that i do want to have a file that is going to be let's do let's do show notes and so i'm going to have a little bit of this and i'm not sure how i want to do these but this is going to be uh let's see it's going to be simple script and today we're going to do is we're just going to connect to our database and i'm just going to connect to one that's a local kind of database so we'll start with their they give us this main.py so we can work with that uh what they give is just a print you know it's a hello world it's basically print hi and then um yeah they're gonna call that we're not gonna worry about that we'll leave that stuff there uh yeah we'll leave that there for now so if i run it uh if i go to terminal and i do python 3 main then it's just going to say you know it's just a hello world okay so now let's do so we're going to connect to the source database because this is we'll need to and in order to do this we're going to need um i need to import a couple things and so we'll see what we need to import we're going to start with import mysql because we know that's going to be needed and i'm probably going to import this because i'm pretty sure that guy's going to be needed now the first thing i want to do is i'm going to define connect to db oh i don't know why he went over here and i don't know that i need anything um i don't think i do i don't need any parameters so basically what i want to do is um let's do my configuration and this is going to be this is some python ace type stuff but give you an idea of what we're doing so we so for this we're going to have a user we're going to have a password and you can probably see some of this stuff if you were to google connect on via python to a database particularly my sql so database is going to be there and we have this thing called raise on warnings which we just need that guy's true uh database is going to be oh that's a good question i think i have this oops i don't have my sequel set up quite right on this one so let's just go look over here so uh if i want to connect here so my database is going to be tutorial and the host is going to be that guy and let's see the password gosh i hope i can remember the password of these so the username was and so then i do return my sql connector dot connect and i'm going to give it this structure most of the stuff you don't need to know uh because it's just sort of a hey we have this there so i'm going to light you know but if you want to see how python works there you go so we're going to connect to it which means we're going to do uh dbc our db database connection equals connect to db and then we are going to grab a cursor uh cursor equals see dot cursor and our query is going to be um let's do something simple let's look at our um did we go to the yeah we said we were going to be on the tutorial database so we're going to go here and let's just do let's describe tables tables and then we're going do cursor.execute the query i'm gonna do a result oops results equals cursor dot fetch all most of this stuff is not going to matter too much and then but um this gives us a start so you can see so for row and results uh print row let's see what happens here if we try to run that then oh so we've got two okay i stepped aside for a second i had a couple of things to install uh so i went in and had to install uh the my sequel and in order to do that whoop me do this uh i do history grab pip 3. so i had to do a pip 3 install my sequel uh actually the key one was i had to do whoop i did this uh throw this in the show notes so i had to do my sequel i also had to do that guy and i think did i have i think i had one other no maybe not [Music] gonna do it here okay so i think i'm good so i had a pair of uh connectors i had to install and so now what we're going to do is um we've got our pair of imports i'm connecting as tutor because my password was test123 connecting my database i guess i can do this to just clean that up get some spaces out of the way i did have a little typo earlier that i fixed i've got raison warnings database host all that goodness and uh if i run it then we're gonna see here that i come in here i come in i'm gonna connect to the server and i go i'm just gonna do show tables get the results back and for each row i'm going to print out the results so each one of these this is a row so i could also do print row zero i think will give me more of a collec yeah so that gives me the actual string so now i can see tables and let me go ahead and for each of these well let's see how i want to do okay so let's do it with that so let's see the same thing we're going to now do um three two one and take this basically the same thing except for now this one we're gonna do describe let's describe address uh yeah because i'm in the right database and so i'm going to come through and same thing and now here i'm gonna see well let's do this just make it easier address columns and so now if we run it we see address columns so here are the columns so we're actually you know we're connecting and we're doing our most basic thing which is we've got a list of tables and we've got some columns for the table so what we're going to do is we're going to end up adding a second connection and this will be connect to database 2 and we're going to make him on a local host and i'm not going to have that set up yet so i'm going to go create that database and that user but that's where we're going to be going next so for now uh this one we came in and this is going to be there'll be links in the show notes for this also you can see you know pretty well what we've got and actually i'm gonna go ahead as part of this and get rid of this little helpful stuff they've got uh let's see we'll get rid of that and we're going to come back we're going to start building some of these things out a little bit more next time around and first thing we're going to do is we're going to do a table compare so we've just you know we played around with it here but this will be our first one will be that we're gonna come in we're gonna start looking at that but i think that's a good time 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 i am into a
slight veering from the sql uh
tutorials and things like that
and want to get into something that's a
little more complicated but we'll still
be
dealing with databases and and how we
can use them and the programming and
things like that
we're gonna focus
uh for this next series of stuff and i
have no idea how long this is gonna take
because
i'm gonna build out an application this
is something that's been a an itch i've
wanted to scratch for a while
years
and it's really it's the idea of a
database synchronizer of some sort uh
you can always do backups and you know
restores and things like that but i want
to do something that is a little bit
more
um
more about the structure
much like some of the tools that are out
there like for example if we've looked
at our django stuff
they have got models and you've got a
way to do migrations and
basically track your database there
and there's some other things i've used
over the years that have similar tools
one that's uh an old library called
torque actually was an excellent one you
would define your database in xml
and then it would it could generate
classes around that
it's something that's a little more
uh targeted and the goal here is to
build a this can be a python application
and you'll see in the notes
i'm going to be using
jetbrains
uh their product called pycharm
but you can also use you can use pretty
much whatever you want if you want to
use like a visual studio code it's got
some pretty good python plugins and
there are definitely other ids out there
this just happens to be the one i'm
going to be using
and our goal is going to be to create
this application that essentially you
can point it at one database and it's
going to look at
differences between that and
a database on another server
so you know whether there are missing
tables missing fields
field types
indexes
you know we're trying to get to all of
it and we'll see how far we can go with
this
because i haven't done it before
i've played around with some of these
concepts and uh you know and this is not
like you would do in a you know in a
django or some of those other tools i
mentioned where you build it from
scratch using this
or some of them do have some sort of a
reverse engineer but you know
essentially where you
build your database you have some way
that you
model it outside of the database itself
for example you know through xml files
or something like that
and then you use those to generate or
update or migrate your database instead
this is going to be point this thing at
database a point it at database b
and let's see if those things exist in
both
and so this is going to be a programming
experiment experiment as well as some of
the other things that we're going to get
into and we're going to be getting into
um
for example we're going to get into
you know pulling describing tables and
generating basically dynamic sql and
some things along those lines
so let's start with uh in this first
little part there is a
new project thing that pycharm will do
and so i went ahead and did that and it
took a little bit because i you know
threw in some libraries and things like
that i do want to have a file that is
going to be
let's do
let's do show notes
and so i'm going to have a little bit of
this
and i'm not sure how i want to do these
but this is going to be uh let's see
it's going to be simple script
and today we're going to do is we're
just going to connect to our database
and i'm just going to connect to one
that's a local kind of database so we'll
start with their they give us this
main.py so we can work with that
uh what they give is just a print you
know it's a hello world it's basically
print hi
and then um
yeah they're gonna call that
we're not gonna worry about that we'll
leave that stuff there uh yeah we'll
leave that there for now so if i run it
uh if i go to
terminal and i do python 3 main
then it's just going to say you know
it's just a hello world okay so now
let's do
so we're going to connect to
the source database
because this is we'll need to
and in order to do this
we're going to need um
i need to import a couple things and so
we'll see what we need to import we're
going to start with import mysql because
we know that's going to be needed
and i'm probably going to import this
because i'm pretty sure that guy's going
to be needed
now the first thing i want to do is i'm
going to define
connect
to db
oh
i don't know why he went over here
and i don't know that i need anything
um
i don't think i do i don't need any
parameters
so
basically what i want to do is
um
let's do my
configuration and this is going to be
this is some python ace type stuff but
give you an idea of what we're doing so
we so for this we're going to have a
user
we're going to have
a password
and you can probably see some of this
stuff if you were to
google connect on via
python to a database
particularly my sql
so database is going to be
there
and we have this thing called
raise on warnings which we just need
that guy's true
uh database is going to be
oh that's a good question
i think i have this oops
i don't have my sequel set up quite
right on this one so let's just go look
over here
so uh
if i want to connect here so my database
is going to be
tutorial
and the host is going to be that guy
and let's see the password gosh i hope i
can remember the password of these so
the username was
and so then
i do
return
my sql
connector
dot connect
and i'm going to give it this structure
most of the stuff you don't need to know
uh because it's just sort of a hey
we have this there so i'm going to light
you know
but if you want to see how python works
there you go so we're going to connect
to it which means we're going to do
uh
dbc our db database connection equals
connect
to db
and then we are going to
grab a cursor
uh cursor equals
see
dot cursor
and our query
is going to be
um let's do something simple
let's look at our
um
did we go to the yeah we said we were
going to be on the tutorial database so
we're going to go here and let's just do
let's describe tables
tables
and then we're going do
cursor.execute the query
i'm gonna do a result oops
results equals
cursor dot fetch all most of this stuff
is not going to matter too much
and then but
um
this gives us a start so you can see so
for row and results
uh print row
let's see what happens here if we try to
run that
then oh so we've got two
okay
i stepped aside for a second i had a
couple of things to install
uh so i went in and had to install uh
the my sequel and in order to do that
whoop
me do this
uh
i do history
grab pip 3.
so i had to do a pip 3 install my sequel
uh actually
the key one was i had to do whoop
i did this uh throw this in the show
notes
so i had to do
my sequel i also had to do
that guy and i think
did i have
i think i had one other no
maybe not
[Music]
gonna do it here okay so i think i'm
good
so i had a pair of uh connectors i had
to install and so now what we're going
to do
is um we've got our pair of imports
i'm connecting as tutor because my
password was test123 connecting my
database i guess i can do this to just
clean that up
get some spaces out of the way
i did have a little typo earlier that i
fixed i've got raison warnings database
host all that goodness
and
uh
if i run it
then we're gonna see here that i come in
here
i come in i'm gonna connect to the
server
and
i go i'm just gonna do show tables
get the results back and for each row
i'm going to print out
the results so each one of these this is
a row so i could also do print
row
zero
i think will give me more of a collec
yeah so that gives me the actual string
so now
i can see tables
and let me go ahead
and
for each of these
well let's see how i want to do okay so
let's do it with that so let's see the
same thing we're going to now do um
three two one and take this basically
the same thing except for now this one
we're gonna do
describe
let's describe address
uh yeah because i'm in the right
database and so i'm going to come
through
and same thing
and now here
i'm gonna see well let's do this
just make it easier
address columns
and so now if we run it we see address
columns so here are the columns so we're
actually you know we're connecting and
we're doing our most basic thing which
is we've got a list of tables
and we've got some columns for the table
so what we're going to do
is we're going to end up adding a
second connection
and this will be connect to database 2
and
we're going to make him on a local host
and i'm not going to have that set up
yet so i'm going to go create that
database and that user but that's where
we're going to be going next
so for now uh this one we came in and
this is going to be there'll be links in
the show notes for this
also you can see you know pretty well
what we've got and actually i'm gonna go
ahead
as part of this and get rid of this
little helpful stuff they've got
uh
let's see
we'll get rid of that and we're going to
come back we're going to start building
some of these things out a little bit
more next time around
and first thing we're going to do is
we're going to do a
table compare
so we've just you know we played around
with it here but this will be our first
one
will be that we're gonna come in we're
gonna start looking at that
but i think that's a good time 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