Everybody welcome to part two of our chat bot with Python and tensorflow tutorial series in this tutorial

We're going to be doing is beginning to build our

Database that's going to store our basically our parent comments - they're paired best reply comments

So the reason why we really want to do something like this is because well first of all

A lot of these files are way way way too big for us to just like read into RAM and then create the training files

from

even just individual months

But chances are you're gonna want to eventually if you wanted to create a really

Nice chat bot you're gonna be wanting to work on many months of data

so maybe possibly billions of comments you do have that your disposal so when that's the case we

Probably want to have some sort of database now for the purposes here. Just to keep things simple

We don't deal with my sequel servers or any other

Big database server type thing I'm just gonna use SQLite

It'll help us get the job done, but you can feel free to use pretty much whatever you want

But I'm gonna use SQLite here now before we get too deep

I just want to address kind of what what all our data should be looking like

So I want to bring up here

This is basically if you downloaded the reddit data, and you know extract it

It should look something like this you should have years

You know basically 2007 to 2015 again bigquery does have data all the way up to

Recent like you know last month whatever that would be

And also if you do go that route. Just know that your formats going to be totally different than ours, so you'll have to adapt

What you're doing?

If you want to go that way, but possibly if someone does share some way to officially pull bigquery

I'll probably append that to the end of this tutorial series because there's there's also multiple models that I'm working on with chatbots

So I also just I'm pretty confident that there will be some follow up videos

Anyways enough that if you click on any of these normally you'll just have all these compressed files

But if you extract them, it looks like this basically and then these files contain just a bunch of samples each sample looks

long

this

Alright, so this is just one sample as you can see there's a bunch of data here. It's obviously a JSON

It's key and value though so yeah, you know there's there's a lot of first of all wasted information here

So just putting into a database will severely

Decrease the size of this data right you just have one column name

And then all the data like you're basically you know this much data becomes just this right that makes more sense

Also, we don't need all of these we don't need like link ID for example. We don't really need name

We might be interested and created. We're probably not interested in when it was received author flare

We probably don't care about you might

We probably don't and so on obviously we do care about like things like score and ups and downs and maybe if they were gilded

Or not or stuff like that we might care about those especially like if trying to make some sort of a very specific bot

Same thing with like the subreddit or something like that if you want it again to create some sort of really specific

Type of chat bot for now I want it to be a fairly general

But I care about at least score one thing to note though is

I'm fairly confident score is miscalculated downs are always zero

So score is always miscalculated if I recall right I can't remember if that's truly the case, but anyway

It's really quick to test. I forget all I know is that take it with a grain of salt because it's improper

Anyway, I'm pretty sure it's the case that downs are always zero

But I can't remember if you can take ups and then score

You know basically ups - the actual score would equal the downs, or if score also always equals ups I can't remember

But anyway just know there's some sort of flaw there

anyway

Let's continue so

Working in Python now what I'm gonna. Go ahead and do is

We're just gonna start building out the code that we're going to be using here

So let's go ahead and import sqlite3 for our database import JSON

And then we'll go from date/time import date time and really

SQLite obviously database JSON to read that format basically and then date time we're really just going to use this to output where we are

As we're kind of outputting just some some logging information just so we know where we are

As you might imagine going through these huge files can take a lot of time so sometimes. I just like to put simple

Outputs that kind of tell us where we are at that at the time

Moving along we're gonna say time frame. I'm gonna say we're going to use 2015. Oh 5 so remember the format of the files

When you download them basically, so we're gonna basically be grabbing this one. They all have RC

I don't know what our C stands for us probably not release candidate, but it's

Reddit comments, maybe I don't know anyway

I don't know what it stands for but anyways they all have that same format obviously. This is May of 2015 so

This is the one that we want

Alternatively you could take lists of time frames and then iterate through them build the database the same way. I'm about to build the database

so

Once we've done that also I'm gonna have SQL

Transaction we're gonna have this because you don't want to be in specially like when you know you're gonna be working with like

millions of rows

You don't want to insert rows one by one if you don't have to that's really inefficient

Instead you want to build up a big transaction, and then do it all at once and it will be

Just gobs faster, so that's what we're going to use that for

Now what we're gonna. Do is build out the connection that's going to be SQLite 3 dot connect

We're gonna connect to something database not seeing the database that would still work though

taht format

And in time frame so this will just be a database called whatever the month and year is

Again alternatively if you wanted what you probably could do is

Like this well for example probably what we're in a color table is like

Parent reply or something like that

Instead you could actually make the database parent reply and then each table name could be the the month or something

To me I don't really think the month and year is all that valuable like there's no real reason why you would separate those out

So I'm not really gonna do that but you could if you wanted

anyway

Then we're gonna define our cursor, so that's just connection dots cursor

Okay now we're gonna. Go ahead and use creator table, so it's fine creates

table

And then this is just going to be your typical see to execute

create table it not

Exists and the tables to be parent

reply and

Then we're gonna have all of our columns so first of all we're gonna have parents ID and

This or. This is gonna be text type and then also it'll be our primary key

Yeah, this is gonna run way off the screen. I think you can get away with a triple quote here

We're gonna find out just

So I don't have to run everything off the screen so much

We'll see how it goes so yeah, so parent ID now we're going to need the comment

Comment ID and that again that's going to be a text

And a not a primary key

But it should be unique

unique

And then we're gonna have parent & parent

Will be also text text type and then we're gonna have the comment itself so the reply

Comment will be text type

I'd also like to go ahead and log the subreddit just simply because I do kind of see in the future

That's gonna be a useful thing to be tracking different subreddits have different ways of talking

And if you want a smarter soundings chatbot you could go with more scientific and engineering types of subreddits if you wanted a more

Nevermind I'm not gonna get myself in trouble well. We'll stop at that anyway. You could get different types of

Chatbots the unix time that's just gonna be an integer and then finally we'll go ahead and take the score

which also should be an int

Okay so with that. We've got our query and of course I did just run it off the screen anyway

But yeah so with that we should create the table if it doesn't exist so then what we do at the end here is if

We'll just start our main loop here our main chunk. I guess maybe name equals main

Let's go to create table, so this will just create the table if it doesn't exist

The other thing to note is if the database doesn't exist when you attempt to connect to it it creates a database

that's why we didn't have to create any database that's obviously just SQLite and

then finally

This obviously will only create the table if it doesn't exist and so it's relatively cheap to run it

So we'll go ahead and run that so

That's all for now what we're gonna. Do is in the next tutorial. We'll actually start working through

I'm not sure if we'll be able to insert any of the data

Too much because there's a lot of cleaning up of the data and stuff, but yeah in the next tutorial

We'll at least start

Buffering through the data and start kind of cleaning up that data and get it ready at least to insert it into the database

Anyways, if you have any questions comments concerns whatever feel free to leave them below, otherwise. I will see you in the next tutorial