Python MySQL Client

MySQL client is a project that is the subdivision of the MySQLdb package. This package provides an interface that runs the Python database API.

Installing mysqlclient

You can easily install mysqlclient with the help of using pip. Type the command “pip install mysqlclient” in the command prompt or the terminal window.

MySQLdb._mysql

It enables you to write APIs that is portable across various databases. It implements the MySQL C API. It follows the features of the object-oriented language as all the functions that require to access MySQL data structures are implemented as the method for the connection object or the result object. The function that does not require any MySQL data structure is called the function of the module.

Implementing _mysql in python

This is a simple program to connect you to the MySQL server on your system. It uses the UNIX socket and you need to use your login name and the password to access the local server.

Connect(): We can pass host, user, passwd and db as the parameters in the connect() function.

host: you need to specify the name of the host with, which you want to establish the connection. By default, the hostname is the localhost.

user: It takes the username and authenticates the user.

passwd: you need to use the password that is set by you while installing the database software to access the server.

db: Specify the name of the database that you wish to access.

Note: You can directly pass the value without the parameter name in the same sequence but assign the values to the keyword parameter as it is easier to read.

Once, you have established the connection to the database server, the next step is to pass the query to operate on the database. Use the db. query() method and pass the query.

Creating a Database file using Python MySQL client:

import sqlite3
#connect() returns a connection object that is stored in con variable
con= sqlite3.connect('firstdb.db')
#cursor stores the query
cur= con.cursor()
#make sure you commit after you make any change in the db and not just in the last
con.commit()
#once done make sure to close the connection
con.close()

Creating a Table in the Database:

import sqlite3
con= sqlite3.connect('firstdb.db')
#cursor stores the query
cur= con.cursor()
# execute() method is used to execute the query on the database
#Cust_id and Cust_name are the two columns of the table
cur.execute('''create table if not exists table_name(
Cust_id text,
Cust_name text

)''')
#make sure you commit after you make any change in the db and not just in the last
con.commit()
con.close()

Adding Values to the Database Table:

import sqlite3
con= sqlite3.connect('firstdb.db')
#cursor stores the query
cur= con.cursor()
# execute() method is used to execute the query on the database
#Cust_id and Cust_name are the two columns of the table
cur.execute('''create table if not exists table_name(
Cust_id text,
Cust_name text

)''')
# below I have stored the query into the query variable and used that to execute the query
query= "INSERT INTO table_name VALUES(101, 'Cust_1')"
cur.execute(query)
con.commit()
con.close()

To fetch the data from the database:

import sqlite3
con= sqlite3.connect('firstdb.db')
#cursor stores the query
cur= con.cursor()
# execute() method is used to execute the query on the database
cur.execute('''create table if not exists table_name(
Cust_id text,
Cust_name text

)''')
# below I have stored the query into the query variable and used that to execute the query
query= "INSERT INTO table_name VALUES(101, 'Cust_1')"
cur.execute(query)
next_query="SELECT* from table_name"
cur.execute(next_query)
#fetchall() method will retrieve all the data from the table and will send that to rows one by one
for row in cur.fetchall():
    print(row)
con.commit()
con.close()

To Return the Result

There are two methods to return the result object. These are:

store_result(): This method is used to return the entire result, which might work if the data is less but in case of huge data it would cause problems to read. We can use the LIMIT clause to limit the rows of the result. But this can be only used when the data is enormous.

use_result(): It stores the result in the server and we can fetch the result line by line using this method.

Functions and Attributes in MySQLdb

• connect(parameters…): It is a python constructor that establishes the connection to the database itself and returns a connection object. The default value to the parameters of the connect is NULL.
• port: It specifies the port number of the connection. Port 3306 is the default TCP port for the MySQL server.
• unix socket: It determines the location of the UNIX Socket.
• compression: This is used to compress the protocol.
• Init_command: It automatically initialises the server when the connection is established.
• connect_timeout: It takes the number of seconds as a parameter if the time is exceeded the operation is aborted.
• read_default_file: It specifies which MySQL configuration file is to be read.
• read_default_group: It states the default group to read.

Note: They are several configuration files and groups the user can select from these options using mysql_option().

• cursorclass: This class is used by the cursor() unless the cursor() is overridden.
• SSL: This parameter establishes an SSL connection to the server if enabled, and if the system and the server do not support the SSL connection, it will throw an exception.
• threadsafety: Multi-threading cannot be handled by the protocols of MySQL. Previous versions used locking to achieve threadsafety. But it was very difficult to accomplish the standard cursor class.
• cony: It is most essential as it is the dictionary or the mapping which decides how are the different types converted from python to MySQL to python and vice versa.

If the key of the dictionary is of MySQL type then there are two possible types of values that can be associated with the key. These are:

• A callable object that returns a python value.
• It can be a sequence of two tuples where the first tuple should represent a flag while the second tuple is a function.

Connection Objects

These objects are returned to us by the connect() function.

Commit(): If any transaction is performed on the database, the transaction should be committed to the database otherwise any function or operation performed will be meaningless as there will be no change in the initial and final state of the database. This function commits the current transaction to the main memory.

Rollback(): If the transactions are supported by the system, this function is used to rollback or reverse the current transaction that is performed on the database, if not so it will raise a not supported error.

Although several more functions are defined on the connection objects, these are most frequently used.