Detailed explanation of several ways to connect to MySQL using Python_python

This article mainly introduces several ways to use Python to connect to MySQL. You can choose a reasonable connection method according to the actual situation. Friends in need can refer to it

Although many NoSQL databases have become popular in recent years It’s brilliant, but relational databases like MySQL are still one of the mainstream databases on the Internet. Everyone who learns Python must learn a database well, whether you are doing data analysis, web crawlers, web development, or machine learning. When studying, you cannot do without dealing with databases, and MySQL is the most popular database. This article introduces several ways to operate MySQL in Python. 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 is a dependency 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 find the corresponding version of whl package download and installation at https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient.

# 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 It is a driver implemented in pure Python. It is not as fast as MySQLdb. Its biggest feature 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 It is cumbersome, the code is repetitive, and there is no object-oriented thinking. Then many wrapper packages and ORM frameworks were 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 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 method that supports Native SQL and tools that support ORM, then 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 that you have almost understood the advantages and disadvantages of these database drivers, you can choose one of them to study systematically and then apply it. I’m in the project. I wish you a happy study. If you don’t understand, you can ask me.

Related recommendations:

Example tutorial of connecting python to sqlite

Detailed explanation of the example of connecting python to impala

How to connect and operate mongodb using python

The above is the detailed content of Detailed explanation of several ways to connect to MySQL using Python_python. For more information, please follow other related articles on the PHP Chinese website!