MySQL And Python- Inserting The Data. — ML For Lazy 2021

Inserting Data Into the database

WOO HOO! We have created the database and learned how to create connections with the database after creating them using MySQL and python. The only thing that remains unanswered is Running the queries to perform the operations on the database. Generally Following main operations are performed on the data ion databases and databases themselves.

THE PURPOSE

This post will see how we can insert the data into the database and store the data permanently in the databases. We use the SQL’s ‘INSERT, INTO’ keywords in the queries for this particular purpose.

The First question I would like to answer is WHAT IS QUERY. So let us answer this question.

WHAT IS A QUERY?

A query is a statement in the MySql, which has the logic of the operations we want to perform on the database or the data stored in the database.

In simple words, it is a command that we give to the MySql so that our purposes are fulfilled, and we can do what we intended to do on the database and the data present in the database.

STEPS FOR QUERY EXECUTION USING PYTHON AND MYSQL

1. Create a connection with the database using credentials, this connection returns the connection object of mysql.
2. Second, you need to create a cursor object, it is like a pointer(mouse), which decides where to click and in this case where to execute query and if the query returns something, then store that returned quantity in itself.
3. This cursor has a method known as execute, which lets us execute the queries passed, on the database whose connection is also passed.
4. Finally if the changes are to be made permanant then we have to commit the changes that rae made in the database.
5. This is the flow of executing the queries using python and mysql.

We must have the mysql-connector-python package installed, in order to work with MySQL. Then we must have imported it.

The first and foremost thing is to create the connection. SO let’s do that.

conn = connection.connect(host="localhost", user="root", password="password",database="database which you created in previous lesson" )

This statement returns the connection objection, i.e., the connection with our passed database with the passed credentials.

The second thing, as per our list, is to create the cursor. So let us do that.

cur = conn.cursor()

Now that we have a cursor, we can execute the queries on the database passed. By default, the database with which the connection is made is passed as we are calling the cursor off that connection.

Let us first write the query and pass that query to the cursor.

query = "CREATE table payments(name varchar(255), address varchar(255))" cur.execute(query)

The execute method lets us execute the queries that are passed to this method.

Let us check whether the query was executed or not. Let us retrieve all the tables in our database.

cur.execute("SHOW TABLES") for table in cur: print(table)

Now let us insert the data into the table. We will create a new table with some more attributes and then insert data into that table.

cur.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

This is another way to execute the queries. Thus passing direct queries into the execute method.

For inserting the data, we need to write the insert query as follows and then execute the same query:

query = "Insert into customers(name, address) values('zack', 'zack 123')" cur.execute(query)

Thus our query was executed, and data was inserted into our table. There is another way of inserting the data, using format specifiers. This works similarly, but we have to pass the query with format specifiers and the values. The values should be in the tuple format.

query = "insert into customers(name, address) values (%s, %s)" val = ('anthony', 'anthony 123') cur.execute(query, val)

The ‘%S’ represents that a string is its value. The first %s will be replaced with the first value in a tuple and the second with the second one, and so on.

We can add more values in the same query. The way we do this is by using the list of tuples in the database.

query = "insert into customers(name, address) values (%s, %s)" val = [ ('Peter', 'Lowstreet 4'), ('Amy', 'Apple st 652'), ('Hannah', 'Mountain 21'), ('Michael', 'Valley 345'), ('Sandy', 'Ocean blvd 2'), ('Betty', 'Green Grass 1'), ('Richard', 'Sky st 331'), ('Susan', 'One way 98'), ('Vicky', 'Yellow Garden 2'), ('Ben', 'Park Lane 38'), ('William', 'Central st 954'), ('Chuck', 'Main Road 989'), ('Viola', 'Sideway 1633') ] cur.executemany(query, val)

These values will be inserted into the table one by one. In this way, we insert the values into the database. Now, as we have made the changes in the database, we need to make these changes permanent, so let us commit the changes using the commit method of the connection object.

conn.commit()

Thus, our changes are made and stored.

Conclusion

These are how we can store the data in the database, either one at a time or many at a time. In the next post, we will see how to get this data back and work with that data. Because in machine learning, we need to work with the data, so we need to get back this data from the database and perform some operations or feed the data to machine learning models for training.

Suppose you have not read how to connect to the database and download MySQL, then see — MySql and Python | Creating Database using Python.

I am a Postgrad student from Kashmir in Computer Science. In these covid days, I turn towards spreading the information about machine learning which is my Passion and Future studies. The aim is to make people understand and understand the basic concepts of Machine and Deep learning myself, which are crucial to further success in this field.

Originally published at https://mlforlazy.in on July 7, 2021.