A few ways to connect MySQL with Python _python

This article mainly introduces the use of Python to connect MySQL several ways, we can choose a reasonable connection according to the actual situation, the need for friends can refer to the next

Although many NoSQL databases have been brilliantly developed in recent years, relational databases like MySQL are still one of the mainstream databases of the Internet, and it is necessary for each Python to learn a database, whether you are doing data analysis, or web crawlers, on-line development, or machine learning, You do not have to deal with the database, and MySQL is the most popular kind of database, this article describes the Python operation of MySQL several ways, you can in the actual development process according to the actual situation reasonable choice.

1, Mysql-python

Mysql-python is also called MySQLdb, is the Python connection MySQL Most popular driver, many frameworks are also based on this library development, unfortunately it only supports python2.x, and the installation of the time there are many preconditions, because it is based on the C development of the library, The Windows platform installation is very unfriendly and often fails, and is now largely deprecated, instead of its derivative version.

# Pre-condition sudo apt-get install python-dev libmysqlclient-dev # ubuntusudo yum install python-devel mysql-devel # Red Hat/cen tos# Installing PIP Install Mysql-python

Windows directly installs by downloading EXE file, public number reply "win" get download link

#!/usr/bin/pythonimport mysqldbdb = MySQLdb.connect (   host= "localhost",  # hostname   user= "John",     # User name   passwd= "Megajonhy", # password   db= "Jonhydb")    # database name # before querying, you must first get the cursor cur = db.cursor () # Execute the native SQL statement Cur.execute (" SELECT * FROM Your_table_name ") for row in Cur.fetchall ():  print (row[0]) db.close ()

2, Mysqlclient

Due to the Mysql-python years of disrepair, and then appeared its Fork version mysqlclient, fully compatible with MYSQLDB, while supporting python3.x, is the Django ORM Dependency tool, if you want to use native SQL to manipulate the database, then recommend this drive Move. The installation method and MySQLdb are the same, Windows can find the corresponding version of the WHL package download installation on the https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient website.

# Windows install pip install some-package.whl# Linux preconditions sudo apt-get install Python3-dev # debian/ubuntusudo yum Install Pyth On3-devel # Red Hat/centosbrew Install mysql-connector-c # MacOS (Homebrew) pip install Mysqlclient

3, Pymysql

Pymysql is a pure Python implementation of the drive, speed than MySQLdb, the biggest feature may be that it is not so cumbersome installation method, but also compatible with Mysql-python

Pip install pymysql# in order to be compatible with MYSQLDB, only add pymysql.install_as_mysqldb ()

An 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 ()

4, PeeWee

The process of writing native SQL is tedious, code is duplicated, there is no object-oriented thinking, and a lot of encapsulation wrapper packages and ORM frameworks are created, and ORM is a mapping between Python objects and database relational tables, with ORM you no longer need to write SQL statements. Improve the speed of writing code, while compatible with a variety of database systems, such as SQLite, MySQL, PostgreSQL, pay the price may be some performance loss. If you're familiar with Django's own ORM, then PeeWee's learning costs are almost zero. It is the most popular ORM framework in Python.

pip install peewee

An 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 Document: Http://docs.peewee-orm.com/en/latest/peewee/installation.html

5, SQLAlchemy

If you're looking for a tool that supports both native SQL and ORM, then SQLAlchemy is the best choice, 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) 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 (n Ame= ' new person ') Session.add (New_person) Session.commit ()

Now almost understand the advantages and disadvantages of these kinds of database drivers, then you can choose one of the system to learn and then apply it to the project, I wish you study happy, do not understand can consult me ha.