Summary of how Python connects to MySQL

This time I will bring you a summary of how Python connects to MySQL. What are the precautions for Python to connect to MySQL? Here are actual cases, let’s take a look.

Although many NoSQL databases have shone in recent years, relational databases like MySQL are still one of the mainstream databases on the Internet. Everyone who learns Python must learn one well. Regardless of whether you are doing data analysis, web crawling, web development, or machine learning, you are inseparable from dealing with databases, and MySQL is the most popular database. This article introduces Python operations. There are several MySQL methods that you can choose reasonably according to the actual situation during the actual development process.

1. MySQL-python

MySQL-python, also called MySQLdb, is the most popular driver for Python to connect to MySQL, and it is also used by many frameworks. Develop based on this library. Unfortunately, it only supports Python2. It is basically deprecated and replaced by its derivatives.

# 前置条件
sudo apt-get install python-dev libmysqlclient-dev # Ubuntu
sudo yum install python-devel mysql-devel # Red Hat / CentOS
# 安装
pip install MySQL-python

Windows can be installed directly by downloading the exe file. Reply "win" to the official account to get the download link

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect(
   host="localhost",  # 主机名
   user="john",     # 用户名
   passwd="megajonhy", # 密码
   db="jonhydb")    # 数据库名称
# 查询前，必须先获取游标
cur = db.cursor()
# 执行的都是原生SQL语句
cur.execute("SELECT * FROM YOUR_TABLE_NAME")
for row in cur.fetchall():
  print(row[0])
db.close()

2, mysqlclient

Due to the disrepair of MySQL-python, its Fork version mysqlclient appeared later, which is fully compatible with MySQLdb and supports Python3.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. For Windows, you can find the corresponding version of the whl package to download and install from the https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient website.

# Windows安装
pip install some-package.whl
# linux 前置条件
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 driver implemented in pure Python and is not as fast as The biggest feature of MySQLdb may be that its installation method is not so cumbersome, and it is also compatible with MySQL-python

pip install PyMySQL
# 为了兼容mysqldb，只需要加入
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, with repeated code and no object-oriented thinking. Then many wrapper packages and ORM frameworks were born. ORM is a combination of Python objects and database relational tables. A mapping relationship, 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 paid may be some loss in performance. If you are familiar with the ORM that comes with Django, then 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 documentation: http://docs.peewee-orm.com/en/latest/peewee /installation.html

5. SQLAlchemy

If you want to find a tool that supports both native SQL and ORM, then SQLAlchemy is The best option, it's 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()

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!

Recommended reading:

How to assign uniform values ​​to array elements in numpy

How to multiply numpy arrays and matrices use

The above is the detailed content of Summary of how Python connects to MySQL. For more information, please follow other related articles on the PHP Chinese website!