How to connect to MySQL using Python

MySQL-python, also called MySQLdb, 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 prerequisites during installation. Because it is a library developed based on C, it is very unfriendly to install on the Windows platform and often fails. It is basically not recommended to use it now and will be replaced by its derivative version.

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

#!/usr/bin/pythonimport MySQLdbdb = 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()

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, then this is recommended drive. 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 / Ubuntusudo yum install python3-devel # Red Hat / CentOSbrew install mysql-connector-c # macOS (Homebrew)pip install mysqlclient

PyMySQL is a driver implemented in pure Python. It is not as fast as PyMySQL. 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()

one example

import pymysqlconn = 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()

The process of writing native SQL is very cumbersome, with repetitive code and 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 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

one example

import peeweefrom peewee import *db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')class Book(peewee.Model): author = peewee.CharField() title = peewee.TextField() class Meta: database = dbBook.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

If you are looking for a tool that supports both native SQL and ORM, then SQLAlchemy is the best choice. It is very close to the Hibernate framework in Java.

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy_declarative import Address, Base, Personclass 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 = engineDBSession = sessionmaker(bind=engine)session = DBSession()# Insert a Person in the person tablenew_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 apply it to the project.

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