Although many NoSQL databases have exploded in recent years, relational databases like MySQL are still one of the mainstream databases on the Internet. Every Python student needs to learn a database, whether you do data analysis, Web crawlers, Web development, or machine learning. MySQL is one of the most popular databases in the world. This article introduces several ways to operate MySQL in Python. You can choose them according to the actual situation during the development process.

1, MySQL – python

Mysql-python is the most popular driver for python to connect to MySQL. Many frameworks are also developed based on this library. Unfortunately, it only supports PYTHon2. x, and there are many preconditions when installing it. It is very unfriendly to install on Windows, often fails, and is now mostly not recommended, being replaced by a derivative.

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

# installation
pip install MySQL-python

Copy the code

Windows directly download the EXE file to install, public account reply “win” to obtain the download link

#! /usr/bin/python
import MySQLdb

db = MySQLdb.connect(
     host="localhost".# the hostname
     user="john".# username
     passwd="megajonhy".# your password
     db="jonhydb")        # database name

You must obtain the cursor before querying
cur = db.cursor()

All native SQL statements are executed
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

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

db.close()

Copy the code

2, mysqlclient

Mysqlclient is fully compatible with MySQLdb and python3. x. It is a dependency on DjangoorM. This driver is recommended. The installation method is the same as MySQLdb. For Windows, you can download the corresponding WHL package from https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient.

# Windows installation
pip install some-package.whl

# Linux preconditions
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
Copy the code

3, PyMySQL

PyMySQL is a pure Python driver, which is not as fast as MySQLdb. Perhaps the biggest feature of PyMySQL is that it is less cumbersome to install, and it is also compatible with mysql-Python

pip install PyMySQL
To be compatible with mysqlDB, just join
pymysql.install_as_MySQLdb()
Copy the code

A case in point

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()
Copy the code

4, peewee

The process of writing native SQL was tedious, repetitive, and without object-oriented thinking, resulting in wrapper packages and ORM frameworks. ORM is a mapping between Python objects and database relational tables. With ORM, you no longer need to write SQL statements. Improved code writing speed and compatibility with multiple database systems such as SQLite, mysql and PostgresQL may cost some performance. If youre familiar with The ORM that comes with Django, PeeWee costs almost nothing to learn. It is the most popular ORM framework in Python.

pip install peewee
Copy the code

A case in point

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)
Copy the code

The official document: http://docs.peewee-orm.com/en/latest/peewee/installation.html

5, SQLAlchemy

If you are looking for a tool that supports both native SQL and ORM, SQLAlchemy is the best choice, and 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()
Copy the code

Now that you almost understand the pros and cons of these database drivers, you can choose one of them to study systematically and then apply it to the project. I wish you a happy learning, and you can consult me if you don’t understand.

Blog: https://foofish.net/python-mysql.html public number: zen Python