MySQL

MySQL Connector  Cursor

To open it from CMD

Step 1 : Windows Key + Run

Step 2 : cd C:\Program Files\MySQL\MySQL Server 8.0\bin

Step 3 : mysql -u root -p

Step 4 : Enter your password

Step 4 : show databases;

Creating a database

mysql> create database simo;

It will show the following:

Query OK, 1 row affected (0.59 sec)

Now, check whether the database ‘simo’ has been created or not

mysql> show databases;

It will show the following

+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| simo |
| sys |
+——————–+

So, now you know that the database has been created.

Now, you would want to create tables etc. However, before that you will have to add a line of code to select the database ‘simo’

mysql> use simo;

After this the system will show this:

Database changed

Now, for creating a table by the name ‘user’ type in the following command:

mysql> create table user(id int(11) primary key, name varchar(30) not null, email varchar(30) not null);

As, you press ‘enter’ the following line will be displayed:

Query OK, 0 rows affected, 1 warning (2.55 sec)

Now, you would want to see the changes, so type in the following:

mysql> show tables;

After pressing ‘enter’ you will see this:

+—————-+
| Tables_in_simo |
+—————-+
| user |
+—————-+
1 row in set (0.26 sec)

This means, a table by the name ‘user’ as been created in the database ‘simo’.

But, here, you cannot see the contents of ‘user’ so you should type in the following command, to describe the table ‘user’:

mysql> desc user;

As, you press ‘enter’ after typing in the command, you will be able to see the description of the table.
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| email | varchar(30) | NO | | NULL | |
+——-+————-+——+—–+———+——-+
3 rows in set (0.09 sec)

Now, lets insert data into the respective columns of the table:

mysql> insert into user values(“1”, “john”, “john@gmail.com”);

The following will be displayed.

Query OK, 1 row affected (0.29 sec)

Now, let’s see if the required changes have been made.

mysql> select * from user;

You see the following:

+—-+——+—————-+
| id | name | email |
+—-+——+—————-+
| 1 | john | john@gmail.com |
+—-+——+—————-+
1 row in set (0.01 sec)

The required changes have been made into the table.

Now, let’s add one more user into the table.

mysql> insert into user values (“2”, “albert”, “albert@gmail.com”);

It was successfully added :
Query OK, 1 row affected (0.15 sec)

Let’s check it then:

mysql> select * from user;

Oh, wow, we can see that a new user has been added into the table.
+—-+——–+——————+
| id | name | email |
+—-+——–+——————+
| 1 | john | john@gmail.com |
| 2 | albert | albert@gmail.com |
+—-+——–+——————+
2 rows in set (0.00 sec)

So, this way you can add as many users as you want.

Now, we will see how to change individual items in a column:

For this, we will use the “update” keyword .

mysql> update user set name=”tom” where id=”1″;

press “enter”.

Query OK, 1 row affected (1.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now, let’s check if the required changes were made into the table.

Using the same “select” command.

mysql> select * from user;

press “enter”.
+—-+——–+——————+
| id | name | email |
+—-+——–+——————+
| 1 | tom | john@gmail.com |
| 2 | albert | albert@gmail.com |
+—-+——–+——————+
2 rows in set (0.04 sec)

So,now we see that the changes were made.

MySQL Connector

First, you’ll have to install the mysql connector. For this type the following in cmd:

pip install mysql-connector-python

After, pressing “enter” you’ll see that the module has been installed.

Now, you need to check the installation. For this enter, the following, command:

import mysql.connector

If it doesn’t give any errors, then it means it has been installed.

Create an object to create a connection

If you want to use a python program to interact with MySQL database then, youl will have to create a connection object first. In the code below, you will see that we have created an object “mydb”, to build a connection.

For this we will use the following syntax.

mysql.connector.connect()

If you run the above code, you’ll get a response like this:

<mysql.connector.connection.MySQLConnection object ar 0x016645F0>

Creating a Database

For creating the database, we’ll use the “create database” statement.

But, before that we will have to create a cursor object.

Just, remember that you’ll execute all the sql commands using this cursor object. Without using this cursor object you can not run any sql quries in python.

we will create a cursor object, in the following manner:

mycursor = mydb.cursor()

See, above that mycursor, is the new object. mydb is the original connection object.

So, the formula is like this:

cursor_variable = connection_object_variable.cursor()

See, you can use both capital letters or small case letters for the statements.

If the above code was executed with no errors, you’ve successfully created a database.

Check, if database has been created

For this we will use the “show databses” statement.

If you run the above code, you’ll see something like the following:

(‘information_scheme’,)
(‘mydatabase’,)
(‘performance_schema’,)
(‘sys’,)

Or you can try to access the database when making the connection:

Primary Key

When creating a table, you should also create a column with a unique key for each record.

This can be done by defining a PRIMARY KEY.

We use the statement “INT AUTO_INCREMENT PRIMARY KEY” which will insert a unique number for each record. Starting at 1, and increased by one for each record.

If the table already exists, use the ALTER TABLE keyword:

Insert Into Table

To fill a table in MySQL, use the “INSERT INTO” statement.

Insert Multiple Rows

To insert multiple rows into a table, use the executemany() method.

The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:

Get Inserted ID

You can get the id of the row you just inserted by asking the cursor object.

Note: If you insert more than one row, the id of the last inserted row is returned.

Cursor

Methods of the Cursor class

The following methods are provided by the “cursor” class.

callproc()
This method is used to call existing procedures MySQL database

close()
This method is used to close the current cursor object.

Info()
This method gives information about the last query.

executemany()
This method accepts a list of parameters list. Prepares an MySQL query and executes it with all the parameters.

execute()
This method accepts a MySQL query as a parameter and executes the given query.

fetchall()
This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones)

fetchone()
This method fetches the next row in the result of a query and returns it as a tuple.

fetchmany()
This method is similar to the fetchone(), but it retrieves the next set of rows in the result set of a query , instead of a single row.

etchwarnings()
This method returns the warnings generated by the last executed query.

Properties of the Cursor class

column_names
This is a read only property which returns the list containing the column names of a result-set

description
This is a read only property which returns the list containing the descriptio nof columns in a result-set

lastrowid
This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT, or, UPDATE operation.

rowcount
This returns the number of rows returned/updated in case of SELECT and UPDATE operations.

statement
This property returns the last executed statement.

 

References

https://www.youtube.com/watch?v=e62RicDmjcY

https://pynative.com/python-mysql-select-query-to-fetch-data/