Several Postures for Connecting MySQL with Python

 

Although many NoSQL databases shine brightly in recent years, relational databases like MySQL are still one of the mainstream databases on the Internet. Everyone who learns Python needs to learn a database well, whether you are doing data analysis or web crawlers, Web development, or machine learning, you can't do without dealing with databases, and MySQL is the most popular database. This article introduces several ways of Python operating MySQL, which you can choose reasonably according to the actual situation in the actual development process.

1、MySQL-python

MySQL Python, also known as MySQL db, is the most popular driver for Python to connect to MySQL. Many frameworks are also developed based on this library. Unfortunately, it only supports Python 2. x, and there are many preconditions for 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 not recommended now, It replaces its derivative version.

 #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

Windows installs directly by downloading the exe file

 #!/ usr/bin/python
 import MySQLdb

 db = MySQLdb.connect(
     host= "localhost" ,     #Hostname
     user= "john" ,         #User name
     passwd= "megajonhy" ,   #Password
     db= "jonhydb" )         #Database name

 #Before querying, you must first obtain the cursor
 cur = db.cursor()

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

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

 db.close()

2、mysqlclient

Because MySQL Python was in disrepair for a long time, its Fork version mysqlclient later appeared, which is fully compatible with MySQL db, and supports Python 3. x, which 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 MySQL. Windows can https://www.lfd.uci.edu/ ~gohlke/pythonlibs/#mysqlclient  Find the corresponding version of the whl package on the website to download and install.

 #Windows installation
 pip install some-package.whl

 #Linux Prerequisites
 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 pure Python implementation driver, and its speed 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
 #To be compatible with mysqldb, just add
 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 tedious. 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 code writing and is compatible with multiple database systems, such as sqlite, mysql、postgresql， The price may be some performance loss. If you are familiar with the ORM provided by Django, the learning cost of peekee 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 documents: 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, 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( two hundred and fifty ))

 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 for systematic learning and then apply it to the project. I wish you a happy study.

---

Source: Liu Zhijun

Original link: Several Postures for Connecting MySQL with Python , Please indicate the source for reprinting!