Python Tutorial

Introduction Python Features Python Applications System requirements for Python Python Installation Python Basics Python Variables Python Data Types Python IDE Python Keywords Python Operators Python Comments Python Pass Statement

Python Conditional Statements

Python if Statement Python elif Statement Python If-else statement Python Switch Case

Python Loops

Python for loop Python while loop Python Break Statement Python Continue Statement Python Goto Statement

Python Arrays

Python Array Python Matrix

Python Strings

Python Strings Python Regex

Python Built-in Data Structure

Python Lists Python Tuples Python Lists vs Tuples Python Dictionary Python Sets

Python Functions

Python Function Python min() function Python max() function Python User-define Functions Python Built-in Functions Anonymous/Lambda Function in Python

Python File Handling

Python File Handling Python Read CSV Python Write CSV Python Read Excel Python Write Excel Python Read Text File Python Write Text File Read JSON File in Python

Python Exception Handling

Python Exception Handling Python Errors and exceptions Python Assert

Python OOPs Concept

OOPs Concepts in Python Classes & Objects in Python Inheritance in Python Polymorphism in Python Python Encapsulation Python Constructor Static Variables in Python Abstraction in Python

Python Iterators

Iterators in Python Yield Statement In Python

Python Generators

Python Generator

Python Decorators

Python Decorator

Python Functions and Methods

Python Built-in Functions Python String Methods Python List Methods Python Dictionary Methods Python Tuple Methods Python Set Methods

Python Modules

Python Modules Python Datetime Module Python Calendar Module  

Python MySQL

Python MySQL Python MySQL Update Operation Python MySQL Delete Operation

Python MongoDB

Python MongoDB

Python Data Structure Implementation

Python Stack Python Queue Python Hash Table Python Graph

Python Advance Topics

Speech Recognition in Python Face Recognition in Python Python Rest API Python Command Line Arguments Python JSON Python Virtual Environment Type Casting in Python Collections in python Python Enumerate Python Debugger Python DefaultDict

Misc

Python PPTX Python Pickle Python Seaborn Python Coroutine Python EOL Python Infinity Python math.cos and math.acos function Python Project Ideas Based On Django Reverse a String in Python Reverse a Number in Python Python Word Tokenizer Python Trigonometric Functions Python try catch exception GUI Calculator in Python Implementing geometric shapes into the game in python Installing Packages in Python Python Try Except Python Sending Email Socket Programming in Python Python CGI Programming Python Data Structures Python abstract class Python Compiler Python K-Means Clustering List Comprehension in Python3 NSE Tools In Python Operator Module In Python Palindrome In Python Permutations in Python Pillow Python introduction and setup Python Functionalities of Pillow Module Python Argmin Python whois Python JSON Schema Python lock Return Statement In Python Reverse a sentence In Python tell() function in Python Why learn Python? Write Dictionary to CSV in Python Write a String in Python Binary Search Visualization using Pygame in Python Latest Project Ideas using Python 2022 Closest Pair of Points in Python ComboBox in Python Python vs R Python Ternary Operators Self in Python Python vs Java Python Modulo Python Packages Python Syntax Python Uses Python Logical Operators Python Multiprocessing Python History Difference between Input() and raw_input() functions in Python Conditional Statements in python Confusion Matrix Visualization Python Python Algorithms Python Modules List Difference between Python 2 and Python 3 Is Python Case Sensitive Method Overloading in Python Python Arithmetic Operators Design patterns in python Assignment Operators in Python Is Python Object Oriented Programming language Division in Python Python exit commands Continue And Pass Statements In Python Colors In Python Convert String Into Int In Python Convert String To Binary In Python Convert Uppercase To Lowercase In Python Convert XML To JSON In Python Converting Set To List In Python Covariance In Python CSV Module In Python Decision Tree In Python Difference Between Yield And Return In Python Dynamic Typing In Python Abstract design pattern in python Builder design pattern in python Prototype design pattern in Python Creational design patterns in Python

How to

How to convert integer to float in Python How to reverse a string in Python How to take input in Python How to install Python in Windows How to install Python in Ubuntu How to install PIP in Python How to call a function in Python How to download Python How to comment multiple lines in Python How to create a file in Python How to create a list in Python How to declare array in Python How to clear screen in Python How to convert string to list in Python How to take multiple inputs in Python How to write a program in Python How to compare two strings in Python How to create a dictionary in Python How to create an array in Python How to update Python How to compare two lists in Python How to concatenate two strings in Python How to print pattern in Python How to check data type in python How to slice a list in python How to implement classifiers in Python How To Print Colored Text in Python How to develop a game in python How to print in same line in python How to create a class in python How to find square root in python How to import numy in python How to import pandas in python How to uninstall python How to upgrade PIP in python How to append a string in python How to open a file in python

Sorting

Python Sort List Sort Dictionary in Python Python sort() function Python Bubble Sort

Programs

Factorial Program in Python Prime Number Program in Python Fibonacci Series Program in Python Leap Year Program in Python Palindrome Program in Python Check Palindrome In Python Calculator Program in Python Armstrong Number Program in Python Python Program to add two numbers Anagram Program in Python Even Odd Program in Python GCD Program in Python Python Exit Program Python Program to check Leap Year Operator Overloading in Python Pointers in Python Python Not Equal Operator Raise Exception in Python Salary of Python Developers in India What is a Script in Python Singleton design pattern in python

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.



ADVERTISEMENT
ADVERTISEMENT