Python MySQL Create and Insert

Hello guys. Welcome to Learnvern.

In the last topic we saw Installation of Database.

About how to install database & the steps of connectivity regarding database.

Today we will see how to create a table & insert record in Database using Python.

Going to Jupyter. I’ll show you all the steps again that we saw in the last topic.

First of all, we have to import.

Import Database API.

Import PYMySQL. We imported it here.

Now we have to make a function for database connectivity.

I’ll write down the Database connection function.

Def CreateConn function.

Return PYMySQL . (dot) connect method.

Under which I have to pass 5 attributes, the ones I told you about in the last topic.

If any of you have not watched the last video yet, kindly check that video first and then continue with this one.

Our first attribute was host, in which we have to write localhost.

Then we will write our database name.

Our database name is Learnvern.

Next is User. User will always be root.

Like I told you in the last video.

Next is the password.

I’ll keep the password blank by default because we haven’t kept any password in the database.

Now Port. If we take Port, the Port number will always be 3306.

If you’ve taken any other port number during the installation, you have to use that number, okay?

So we created a database connectivity function here.

Whenever we have to create a database connection, we will call this function.

Now we have to create a table.

Table create function.

I’ll make a function to create a Table.

Create Table. First of all, I’ll establish a connection here.

So to establish a connection, I took a variable Conn first.

And I’ll call my CreateConnection function.

So whenever you want to create a connection or configuration connection with Database, you can call the Create Connection function directly.

Now we have to write Query.

But before executing the query, you need the support of 1 thing that is the cursor.

I made a variable named cursor here where we used Conn . (dot) cursor.

So we will create the cursor through the cursor function.

Cursor is helping to execute your query.

Now we will write a query here. I took a variable named query here.

We will write a query to create a table here.

Which query will we fire here?

We will fire the Create table query.

Create Table. Then we have to write the table’s name.

I named it Student. Then you have to write datatypes in it.

Your field name and its datatypes.

I want the 1st field to be student ID.

I took a field name SID, need an integer so I wrote Int, primary key as I want to define the primary key, and auto increment. Meaning it will generate automatically.

What else do I need?

I need the students' name, name and define their datatype as VARCHAR.

I'll take its length 50.

In the same way, I want the student’s email ID.

So I’ll write Email VARCHAR. 50.

In a similar manner, I want students’ city. VARCHAR 50.

I wrote my query here.

How many fields did I take? SID that is Student ID, Student Name, Email ID & City.

Now I have to execute this. How will we execute?

We will execute it with the help of a cursor.

You have to use the Cursor . (dot) execute method & pass the query named variable.

It is mandatory to write a commit here. So…

Connection . (dot) commit.

As I said, writing a commit is a must here.

Commit method saves your data in the database.

Then we have to close the connection.

I’ll write a print here & a message that “Table Created”.

So that I get to know that my table is created successfully.

Now I will call this method so that my table is created once.

Create Table. Calling Create Table Function.

I’ll run it.

It is giving me an error in the console.

It is saying Table Student already exists.

We will run it once again.

It showed the message Table Created.

We will go to the database to make sure if my table is created or not.

Well, it is not showing me anything right now.

Right click & refresh.

You can see that the table is now visible, right?

With name, email & city. According to the fields we chose.

So finally, our table is created.

Now we have to insert our data into the table using Python.

We shall go back to Jupyter.

I’ll open a new cell & markdown Insert Record in Database.

We have already written all the steps. 

Now we have to directly make a function here, the Insert function.

So that we can insert our data through that function.

Def Insert data function, in which I have to pass an argument.

Why arguments? Because we have to take those arguments from the users.

We will store the dynamic data.

Let’s see how.

I need Students’ name, email & city.

Our ID is auto incremented so it will be generated automatically.

We have to create the connection again.

Conn, Create Connection.
I’ll write here Creating Connection.

Now we want our cursor.

We cannot fire a query without a cursor so we will create a cursor.

We created a cursor here.

We’ll have to bring in the function, the argument we’ve written here, the ones we have passed in the function argument.

Only then can we send whatever the user sends in the query.

For that, we’ll make a variable argument that means ARGS & I passed my argument in it.

So my arguments have been passed.

Now we will write our query.

We have to write the Insert query. Insert query.

Insert into table name. Our table name is Student.

Then our field names. What are those?

Name, Email & City.

See, the fields are Name, Email & City. right?

We passed it here.

Next we’ll have to pass the values, so values.

We need dynamic values through users.

We will pass %S here.

Pass what? %S.

You will pass the %S according to the number of fields.

%S will pass the values derived from the arguments.

Now we have to execute.

Cursor . (dot) execute function.

And pass a query first of all. Meaning query variable and then pass the argument.

Then we will save it. Which function do we have to save it?

Commit function. Conn . (dot) commit.

I’ll write here “saving the data using the commit function.”

Then I'll print “Data Inserted.”

At the end, we will do connection . (dot) close.

Understood?

So we made an insert data here.

Now we will Run it. But before running it, we will have to pass these 3 attributes.

We’ll make a variable for these attributes.

N for Name. I’ll take an Input here to take an input from the user.

Enter your name.

E for Email. Input. Enter your email.

And C for City. Input. Enter your city.

Now we have to call this insert function.

I’ll directly write the insert function here.

And here in argument, we will pass N, E & C.

Because we are storing the data we take from the users in these 3 variables.

Now as soon as I run it, it will ask me to fill data.

Sunit. Email ID, S @ gmail.com.

And in the city, Ahmedabad.

I’ll run it. A message showed up saying Data Inserted.

Now we will check it by refreshing.

Our data has been inserted here.

This is how the data is inserted in the database.

Understood all the points?

So what did we learn today?

We made a Create Table function first, in which we created a table through Python.

Then we entered the data in that table database.

Clear?

In the next video, we will learn how to Fetch the data as well as how to Fetch data by ID.

Okay? Thank you.