Row,row)Įlse: # The video doesn't exists so we will add it to a temp df and If check_if_video_exists(curr, row): # If video already df.iterrows() returns two parameters i which is the row index and row which is the row as a tuple, but in our case, we don’t need to use i, just row. Row and other columns represent the columns in the pandas dataframe where our for loop is going row by row.
Curr for both functions is the database connection. Our for loop with the functions now looks like below. Again, let’s wrap it in a function:ĭef update_row(curr, video_id, video_title, view_count, like_count, The %s are just parameters for variables so that we can insert the proper values in the SQL command. Vars_to_update = (video_title, view_count, like_count, dislike_count,
#Import data into python update
Now we should update those database records with the new counts we extracted from the Youtube API that’s saved in our pandas dataframe. Let’s say our video exists in the database table. This will also work for an initial load since the videos will not exist since there are no videos in the table.
#Import data into python generator
errows is a generator which yields both the index and row (as a Series). If it does not, append the video information to the database table.
#Import data into python code
If check_if_video_exists(): # If video already exists then we will updateĮlse: # The video doesn't exists so we will append to the db tableīasically, what this code is saying is to go through the dataframe one row at a time using iterrows() and check to see if the video exists. But we want to perform an UPDATE if the video exists and we want to append a new video to the database table if the video doesn’t exist in the table. This will help us load all our videos to the database for the first time and for all following times after. Let’s check to see if a video exists first. Now we can connect to the database, name our table, and create the table on the database in the cloud with these three lines of code. Because if we pass a table name without this function it will read like “video” with the quotes in the SQL query, so we use AsIs() to tell postgre that you should take this as the name without the quotes. AsIs() is a function that is used to take off the quotes around the table name. We can do that by passing the table name in our variable tablename using. The first part of the execute() method requires the SQL CREATE TABLE command which is saved in create_table_command and since there’s a parameter %s that represents the table name, we need to also pass the table name into the command. We’ll then use the execute() method to our cursor() class to execute the SQL command. Upload_date DATE NOT NULL DEFAULT CURRENT_DATE,Īnd just like with creating the connection to the database, we’ll wrap everything in a function called create_table.Ĭurr.execute(create_table_command, )
We then save everything in a variable named create_table_command.Ĭreate_table_command = ("""CREATE TABLE IF NOT EXISTS %s ( Since we know the table schema already because we have the pandas dataframe of our data, we’re just going to name the columns and add the data types. We have a CREATE TABLE IF NOT EXISTS and then the parameter %s where %s is the placeholder for my table name. This makes it easier and reduces human error when you’re experimenting and testing. We’re going to use SQL to obviously create the table but we’ll add a variable %s to our CREATE TABLE command so that we can change the table name without touching the actual SQL command.
0 kommentar(er)
