Although many NoSQL databases shine in recent years, relational databases such as MySQL are still one of the mainstream databases on the Internet. It is necessary for everyone who studies Python to learn a database well. No matter you are doing data analysis, Web crawler, Web development or machine learning, you can't do without dealing with the database, MySQL is the most popular database. This article introduces several ways Python operates mysql. You can choose reasonably according to the actual situation in the actual development process.
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. Moreover, there are many preconditions during installation, because it is a library developed based on C. It is very unfriendly to install on Windows platform and often fails. It is not recommended to use it now, instead of its derivative version.
# Preconditions sudo apt-get install python-dev libmysqlclient-dev # Ubuntu sudo yum install python-devel mysql-devel # Red Hat / CentOS # install pip install MySQL-python
Windows is installed directly by downloading exe files, official account reply to "win" for download link.
#!/usr/bin/python import MySQLdb db = MySQLdb.connect( host="localhost", # host name user="john", # user name passwd="megajonhy", # password db="jonhydb") # Database name # The cursor must be obtained before querying cur = db.cursor() # Native SQL statements are executed cur.execute("SELECT * FROM YOUR_TABLE_NAME") for row in cur.fetchall(): print(row) db.close()
Since MySQL Python was in disrepair for a long time, its Fork version mysqlclient appeared later, which is fully compatible with MySQL dB and supports Python 3.0 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. Windows can be installed in https://www.lfd.uci.edu/~gohl... Download and install the corresponding version of whl package on the website.
# Windows setup 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
PyMySQL is a pure Python driver, which is not as fast as MySQL dB. The biggest feature may be that its installation method is less cumbersome and compatible
MySQL-python pip install PyMySQL # To be compatible with mysqldb, you only need to add pymysql.install_as_MySQLdb()
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()
The process of writing native SQL is very cumbersome, the code is repeated, and there is no object-oriented thinking. Then, many wrapper packages and ORM frameworks are 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 may be some loss of performance. If you are familiar with Django's own ORM, the learning cost of peewee is almost zero. It is the most popular ORM framework in Python.
pip install peewee
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...
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(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 you almost understand the advantages and disadvantages of these database drivers. Next, you can choose one of them for systematic learning and then apply it to the project. I wish you a happy study. If you don't understand, you can consult me.