what's going on buddy welcome to part 5
of our chat bot with Python and
tensorflow tutorial series in this
tutorial what we're going to be doing is
hopefully actually putting things into
our database so let's go ahead and get
started so basically where we left off
was here and we wrote our final bit of
code was finding out whether or not the
data was acceptable and the data here in
this case is going to be the body text
basically the text of the comment should
we even consider it
so initially I've always actually put
this right before all inserts just
because that's like the order of
operations in my head but actually score
is super cheap so we might as well do
that so if the score is greater than 2
sure but then what we're gonna do is I
say if acceptable body then let's do
these other operations I think that's a
better way to do it rather than go
through all of these operations and then
ask to look for basically go through all
of this only to find that the comment
isn't acceptable
although most comments are probably more
than acceptable so I don't know I don't
really sure what the best way to go
about it is to be healthy anyway we'll
do that it should be pretty cheap to do
it anyways so that's alright and really
after basically at after the score is
high enough we're pretty we're going to
insert the question is are we going to
insert this as a new row or are we going
to insert this as an update so no matter
what we're going to insert no matter
what that means we're going to ask this
question so I suppose this could save us
in theory a little bit of processing
really long well I guess in this case it
wouldn't like if this was not true
whatever anyways the show must go on
so if the score is greater than the
score and the the comment itself happens
to be acceptable then what do we want to
actually do well if this is all the case
then we're gonna go ahead and we would
like to do a SQL insert replace
please comment ok and for now I'm just
gonna leave that there I'm just going to
put this here so I can quickly find it
now if there isn't an existing comment
score what we're gonna want to do is
come down here and we're going to say
else and we've already checked if it's
acceptable so then we're going to ask if
parent data so if there is a parent that
we have data for then what we want to do
is SQL insert has parent and then and
then otherwise else SQL insert nope okay
so that's what we basically have these
three different functions that we're
gonna work with now chances are there's
probably better way to do it but that's
what I'm gonna do so so now we're gonna
pass the information to these so
basically if we're gonna be replacing
something that means we're going to take
comment ID we're gonna take a parent ID
we're probably gonna take the parent
data because we got it the body so
that's gonna be the new comment so this
will be parent this will be the reply
and then subreddit created UTC and then
the score so that's if we do have we
have an existing comment already in
place and we're gonna update it because
our new score is higher now if we do
have that parent in our database so we
have information on that we want to go
ahead and insert with that that
information that we do have so in this
case it's going to be again a comment ID
and in fact
ooh trying to think if that would be
like it should be every it should be the
exact same I'm pretty sure it should be
exactly the same data he is here because
we're we have all that information it's
really only this one here with no parent
that we we don't have anything to no
parent body information to throw in so
this one would be comment ID we do have
a parent ID because everything has a
parent ID if it's a top-level comment
and it has no parent comment the actual
parent is the threat itself the reddit
thread so anyway parent ID but we don't
have parent data but we do have body
subreddit created UTC and it's for so
why would we insert these if we don't
have parent information to go with them
well because this comment might still be
some other comments parent that we want
to get the data on so that's why we
actually still want to we still want to
store that information yeah so now what
we have to do is actually build all
three of these inserts so there are all
three pretty similar part of me wants to
build them all I guess we'll just build
them all together but yeah let's just
let us throw them down here like I said
there's probably better way to do this
then then create them like this but I'm
gonna go it this way so define SQL
insert through a place comment we've got
common ID we've got parent ID we've got
parent comment subreddit time and the
score and then again in this case let's
say just in case we hit an issue we'll
try accept
as E and then on in this case we'll say
print I don't know replace comments and
then whatever II was like you have to
stringy or not I'm gonna go ahead and
pass that though I don't think we have
any other ease just precise uh stuff
that we comment it out I've been playing
and go too much recently I can't
remember if you have to throw straight
here not anyways yeah so we want to have
that information there so now we're
gonna say SQL equals and again we'll
just use triple bullets here and then
we're gonna say update updates parent
reply set undecided if I really want to
write all this out or if I just want to
post it and then I think I'm just gonna
copy and paste this I'm not sure what
game we're gonna have by writing out all
these queries I'm gonna copy and paste
them so I'll put a link in the
description to the text-based version of
this tutorial for forget someone
reminding me but if I even if I do
forget it'll be live on Python
programming that so you should be able
to find it but yeah I just don't see any
benefit to writing all this out so
anyway here we have the three functions
so basically what's happening is in this
case well first of all I want to update
this this should be s I don't know
update and then we'll call this one
parent no parents
okay so basically what this is going to
do is if because it has it's just gonna
overwrite basically so what we want to
do is we want to overwrite all this
information basically where the parent
ID was whatever that comments parent ID
was because so basically anytime we've
got that parent ID any reply to that
parent comment we want to make sure
that's the new comment that has a better
score and then SQL has pink insert has
parent basically what this one's doing
is just we're just inserting where there
was a parent ID or basically what we're
saying we're inserting a new row right
where we have the parent ID but we also
happen to have the data for that parent
so we're inserting information about
that parent body basically and then in
this one we're inserting there was no
parent but we want to have the parent ID
just in case somehow maybe it was out of
order but also mainly we're inserting
this one so we have parent information
for another comment whose parent might
be this comment okay yeah we just saved
probably fifteen minutes doing doing it
that way mm-hmm so anyways but if you
have any questions or whatever you can
feel free to ask but it's all pretty
simple SQL queries there so now the last
thing I want us to go ahead and do is so
we can actually press GO on this script
to make sure it works is define the
transaction builder so up to this point
we've been we've been making you know
we've been building these queries but as
you can see here we're using transaction
builder and passing the SQL so now we're
gonna do is add a final little helper
function and that's going to be defined
for not in all caps though and in fact
let's just copy that define transaction
builder it takes in SQL statements and
then it does something that's gonna piss
some people off and it's gonna go Lobel
the SQL transact
so that's global globulin the this
variable here so we're going to global
that that way we can because we so we
can stuff things into the SQL
transaction but eventually we want to
actually clear it out so we're in a
global for that reason so now we want to
do is we'll come down here and so we're
gonna it's going to take in some SQL
basically we're gonna do is we're just
going to keep building this transaction
until it's of a certain size so what
we're gonna do is we're just going to
say SQL transaction dot append the SQL
statement so we're just keep appending
these SQL statements to the transaction
and then we're gonna say if the length
of the SQL transaction is greater than a
thousand you could choose different
numbers a thousands not gonna be all
that much slower than ten thousand but a
thousand is gonna be a whole hell of a
lot faster than one or ten or something
like that anyway C dot execute and then
to do this to insert like a bulk
statement you need to begin Trant
transaction here we go so so we start
the transaction that way and then we're
just gonna say for s in SQL transactions
so for each of those little SQL
statements what do we want to do well
we're gonna try to see dot execute s
otherwise we're just going to accept and
commit a holy sin of pass and and then
when we're all done once we've we've
said execute all this stuff we're gonna
run a commit you also could execute
commit but I'm gonna go ahead and just
do connection commit because there's a
method for us and then once we've done
all that what we want to do is SQL
transaction equals nothing we want to
empty it out ok looks good very good so
now we should be ready let's go ahead
and save that we should be ready to
actually run this code and see what what
we have so let me
see if we pause this and then I'll pull
up where the database should be all
right if I did everything right which
I'm sure I didn't this database here
should start to be populated so let's go
ahead I'm just gonna press f5 to run
this yeah we've done everything else
that we really wanted to do I guess one
thing I want to add before we get too
deep because I just I'm gonna not run
this whole thing on video I just really
want to make sure it works is basically
in this for loop we'll just come down
here I think that's right should be what
to one tab to tab three tabs over one
two three then what we're gonna say is
if row counter modulo 100,000 equals
zero so for every 100,000 rows let's go
ahead and print total rows red paired
rows time and then we'll go ahead and
format the row counter paired rows
paired rows
oh we also didn't do the code for the
pair drizzle anyway we'll add that
moment and then string date/time dot now
so paired rows basically the only time
we you know if we're updating an
existing comment that's not a new pair
so we don't really need to do it there
also are we okay we are incrementing the
row counter anyway
if there is parent data that means we're
inserting and this will be the first
reply we've got so really its well it's
after this one that we definitely want
to say paired rows plus equals one but
then here when there's no parent that's
not a pair so we don't really need
anything else there either so we really
just need to throw it in that one
okay let's run this and see what errors
we've got invalid syntax not a surprise
here that's not something we can say so
these would be a double equal assignment
versus comparison let's go again do we
Wow I made the same exact mistake okay
comment ID is not defined so I got this
error here SQL insert parent comment ID
did we not do a underscore possibly oh
we just simply never defined the comment
ID interesting so let's just say comment
underscore ID equals row and this one
was kind of funny it I think it was row
name so let's try this again and it does
appear that the database is growing in
size I think I'll just wait until the
very first hundred thousand rows I
forget what it's called as so I'm sorry
viewer I forget what the name of this is
called for viewing the database anyway
okay so for the first 100,000 rows we've
paired 3220 comments that sounds about
right especially because those first
batches probably gonna have too many
parents for sure historically but as we
continue to build we should get a few
more pairs per hundred thousand rows but
anyways I'm gonna go ahead and stop this
and make sure our database looks
appropriate let's open that up
[Music]
ok let's browse the data okay that's a
correct comment ID that's good so as you
can see here this is our database
basically a handheld what's a vape why
not reduce the volume of the tank here
this one should make a little more sense
it's hot have you seen her pick she's
supposedly 31 probably a meth head like
read it okay don't tell me when to sleep
you're not my real mom okay anyway
clearly this is working subreddit okay
and then score and all that so sure
enough all of these scores are higher
than one anyway so we can keep running
that keep building a massive database of
parent and reply information basically
what you should do is at least run that
for the entire 2015 a 2015 data set but
if you really want to have a good chat
buy it you'd probably them continue to
build that even even larger than that
for example that the current chat bot
was built off like 20 million pairs it's
so-so
quite a bit larger of a data set than
what you're gonna get off just this one
this one dump the newer stuff that we're
the other model that I'm gonna be
working on though might require a little
less data I really don't know at this
point but we'll find out in time anyways
go ahead and build that out like I said
do at least May 2015 if you can or if
that single dump I can't remember what
that single dump was it was sometime in
2015 I can't remember if it was January
or May just do the at least one entire
month in 2015 let's say anyway if you
have questions comments concerns if I
made a mistake which it probably did
somewhere feel free to below otherwise I
will see you guys in the next tutorial
which is where we start talking about
models and all that fun stuff how we I
can actually take this training data
that we've been building and convert it
into training data except this
we're just kind of paring things
together we don't really have a perfect
train set so the next thing we have to
do is actually build the training data
and then stuff it through some models
somewhere so that's what you guys have
to look forward to again questions
comments concerns whatever feel free to
leave them below otherwise I'll see you
in another tutorial