Although many NoSQL databases shine in recent years, relational databases such as MySQL are still one of the mainstream databases on the Internet. It is necessary for everyone who studies Python to learn a database well. No matter you are doing data analysis, Web crawler, Web development or machine learning, you can't do without dealing with the database, MySQL is the most popular database. This article introduces several ways Python operates mysql. You can choose reasonably according to the actual situation in the actual development process.
1,MySQL-python
MySQL python, also known as MySQL dB, is the most popular driver for Python to connect to MySQL. Many frameworks are also developed based on this library. Unfortunately, it only supports Python 2 x. Moreover, there are many preconditions during installation, because it is a library developed based on C. It is very unfriendly to install on Windows platform and often fails. It is not recommended to use it now, instead of its derivative version.

# Preconditions
sudo apt-get install python-dev libmysqlclient-dev # Ubuntu
sudo yum install python-devel mysql-devel # Red Hat / CentOS

# install
pip install MySQL-python

Windows is installed directly by downloading exe files, official account reply to "win" for download link.

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(
     host="localhost",    # host name
     user="john",         # user name
     passwd="megajonhy",  # password
     db="jonhydb")        # Database name

# The cursor must be obtained before querying
cur = db.cursor()

# Native SQL statements are executed
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

for row in cur.fetchall():
    print(row[0])

db.close()

2,mysqlclient
Since MySQL Python was in disrepair for a long time, its Fork version mysqlclient appeared later, which is fully compatible with MySQL dB and supports Python 3.0 x. It is a dependent tool of Django ORM. If you want to use native SQL to operate the database, this driver is recommended. The installation method is the same as MySQLdb. Windows can be installed in https://www.lfd.uci.edu/~gohl... Download and install the corresponding version of whl package on the website.

Add description

# Windows setup 
pip install some-package.whl

# linux prerequisites
sudo apt-get install python3-dev # debian / Ubuntu
sudo yum install python3-devel # Red Hat / CentOS
brew install mysql-connector-c # macOS (Homebrew)

pip install mysqlclient

3,PyMySQL
PyMySQL is a pure Python driver, which is not as fast as MySQL dB. The biggest feature may be that its installation method is less cumbersome and compatible

 MySQL-python
pip install PyMySQL
# To be compatible with mysqldb, you only need to add
pymysql.install_as_MySQLdb()

An example

import pymysql
conn = pymysql.connect(host='127.0.0.1', user='root', passwd="xxx", db='mysql')
cur = conn.cursor()
cur.execute("SELECT Host,User FROM user")
for r in cur:
    print(r)
cur.close()
conn.close()

4,peewee
The process of writing native SQL is very cumbersome, the code is repeated, and there is no object-oriented thinking. Then, many wrapper packages and ORM frameworks are born. ORM is a mapping relationship between Python objects and database relational tables. With ORM, you no longer need to write SQL statements. It improves the speed of writing code and is compatible with a variety of database systems, such as sqlite, mysql and postgresql. The price may be some loss of performance. If you are familiar with Django's own ORM, the learning cost of peewee is almost zero. It is the most popular ORM framework in Python.

pip install peewee

An example

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print(book.title)

Official documents: http://docs.peewee-orm.com/en...
5,SQLAlchemy
If you want to find a tool that supports both native SQL and ORM, SQLAlchemy is the best choice. It is very close to the Hibernate framework in Java.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
 
from sqlalchemy_declarative import Address, Base, Person

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street_name = Column(String(250))
 
engine = create_engine('sqlite:///sqlalchemy_example.db')
Base.metadata.bind = engine
 
DBSession = sessionmaker(bind=engine)
session = DBSession()
 
# Insert a Person in the person table
new_person = Person(name='new person')
session.add(new_person)
session.commit()

Now you almost understand the advantages and disadvantages of these database drivers. Next, you can choose one of them for systematic learning and then apply it to the project. I wish you a happy study. If you don't understand, you can consult me.