1. Introduction

In crawler, automation, data analysis, software testing, Web and other daily operations, in addition to JSON, YAML, XML, there are some data often used, such as Mysql, Sqlite, Redis, MongoDB, Memchache and so on

Generally, we use a specific client or command line tool to do this; But when it comes to engineering projects, integrating this part of the data manipulation into the code is the way to go

In the next few articles, I’ll talk about how best to manipulate this data in Python

This article starts with the most widely used relational database – Mysql

2. Prepare

First, we create a database XH either from the Mysql client or from the command line

Next, create a simple table called people under this database

For demonstration purposes, only three fields are created: ID, Name, and AGE, where ID is the primary key

There are three ways to operate Mysql in Python:

  • Python-MySql

  • PyMysql

  • SQLAlchemy

Among them,

Python-mysql is built with C syntax, and the interface is refined for best performance. However, due to multiple environment dependencies and complex installation, the update has been stopped and only Python2 is supported

PyMysql is designed as an alternative to Python-mysql. It is written in Pure Python. It is easy to install and supports Python3

SQLAlchemy is a very powerful ORM framework that is widely used in Python Web programming. It does not provide low-level database operations, but mainly defines the model to the data table structure

Since Python-mysql does not support Python3, this article covers only the last two operations

3. PyMysql

First, install the dependency using PIP

Pip3 install PymysqlCopy the code

Connect to the database, obtain the database connection object and cursor object

Use the connect() method in Pymysql to get the database connection object by passing in the HOST address of the database, port number, username, password, and name of the database to be operated on

Then, through the database connection object, obtain the cursor object that performs the database specific operation

Self.db = pymysql.connect(host='localhost', port=3306, user='root', password='**', Database ='xh') # Self.cursor = self.db.cursor()Copy the code

Next, we will implement the operation of add, delete, change and check

1, the new

New Contains the new single data and multiple data

To insert a single piece of data, you simply write an inserted SQL statement, execute the cursor object’s execute(SQL) method as an argument, and commit the data to the database using the database connection object’s commit() method

SQL_INSERT_A_ITEM = "INSERT INTO PEOPLE(name,age) VALUES('xag',23);" Def insert_a_item(self): """ self.cursor.execute(SQL_INSERT_A_ITEM) self.db.commit() except Exception as e: Print (e) self.db.rollback()Copy the code

You can insert more than one piece of data at a time using the executemany() method that executes the cursor object, passing in the inserted SQL statement and a list of location variables

Select * from SQL where name and age are variables; SQL_INSERT_MANY_ITEMS = "INSERT INTO PEOPLE (name, age) VALUES(%s, %s)" # INSERT INTO PEOPLE (name, age) VALUES(%s, %s) 24), (" detective ", 25)] def insert_items (self) : "" "insert multiple records: return: "" "try: self.cursor.executemany(SQL_INSERT_MANY_ITEMS, self.datas) self.db.commit() except Exception as e: Print (" insert data exception ") self.db.rollback()Copy the code

Note that PyMysql treats all fields in SQL statements as strings, so the age field is treated as a string in SQL

2, the query

The query is divided into three steps, which are:

  • Execute a specific SQL statement through a cursor object

  • Through the cursor object, the tuple data is obtained

  • Walk through the tuple data to see the results

For example: view all the records in a data table

SQL_QUERY_ALL = "SELECT * FROM PEOPLE;" def query(self): Self.cursor.execute (SQL_QUERY_ALL) self.cursor.fetchall() self.cursor.fetchall() self.cursor.fetchall() self.cursor.fetchall() id = row[0] name = row[1] age = row[2] print('id:', id, ',name:', name, 'age:', age)Copy the code

To query a record based on a condition, you only need to modify the SQL statement

SQL_QUERY_WITH_CONDITION = "SELECT * FROM PEOPLE WHERE id={};" Self.cursor.execute (sql_query_with_condition.format (5))Copy the code

3, update,

Similar to the new operation, the update operation also uses the cursor object to execute the updated SQL statement, and finally uses the database connection object to actually update the data to the database

SQL_UPDATE = "UPDATE PEOPLE SET name='%s',age=%s WHERE id=%s" def UPDATE (self): """ return: """ sql_update = sql_update % (" sql_update ", 30, 5) print(sql_update) try: Self.db.com MIT () except Exception as e: self.db.rollback() print(' update data ') print(e) self.db.execute (sql_update) self.db.com MIT () except Exception as e: self.db.rollback() print(' update data ')Copy the code

4, delete,

The deletion operation is similar to the query or add operation. You only need to change the SQL statement

SQL_DELETE = "DELETE FROM PEOPLE WHERE id=%d" def DELETE (self): """ return: """ try: SQL_DELETE % (5) self.db.com self.db.com () except Exception as e: Rollback self.db.rollback() print(e)Copy the code

Finally, we need to release the cursor object and database connection object resources

Def teardown(self): # self.cursor.close() self.db.close() self.db.close()Copy the code

4. SQLAlchemy

First, using SQLAlchemy to manipulate Mysql databases again requires the installation of dependency libraries

# install dependencies pip3 install sqlAlchemyCopy the code

Create a Base class declarative_base() through the built-in method declarative_base() of SQLAlchemy

Then, define a custom subclass of the Base class that internally defines static variables that correspond to the fields in the table people

from sqlalchemy import Column, Integer, String, Create_engine from sqlalchemy. Ext. Declarative import declarative_base Base class # Base = declarative_base () # custom table class People(Base): # select * from 'Column' where id = 'Column' Primary_key =True) name = Column(String(255)) age = Column(Integer) def __repr__(self): """ """ return "<People(id:{},name:{},age:{})".format(self.id, self.name, self.age)Copy the code

Next, assemble a database connection address from the database name, user name, password, and Host and pass it as a parameter to SQLAlchemy’s create_Engine () method to create a database engine instance object

# Create engine instance object for database Xh engine = create_engine("mysql+pymysql://root: database password @localhost:3306/xh", encoding=" UTF-8 ", echo=True)Copy the code

Finally, the database engine creates the table structure in the database and instantiates a session object

Note that the checkfirst parameter in the create_all() method, if passed True, will determine if the table exists, and if it does, it will not be recreated

Create table structure # checkfirst Self.session = sessionMaker (bind=engine)() self.session = sessionMaker (bind=engine)Copy the code

In this way, all the preparation work has been completed, and the next operation can be added, deleted, modified and checked

1, the new

The new operation also involves inserting one record and multiple records corresponding to the add() and add_all() methods of the session object

To add a record, simply instantiate a People object and perform the add(instance) and commit() methods of the session object above, inserting data into the table

Def add_item(self): """ Self.session.com it() self.session.comit() self.session.comit() self.session.comCopy the code

To insert more than one piece of data at a time, just call add_all(list data)

Def add_items(self): """ return: "" "datas = [People (name = 'zhang' age = 20), People (name = 'bill', the age = 21), People (name = 'Cathy', the age = 22), ] self.session.add_all(datas) self.session.commit()Copy the code

2, the query

Query (variable parameter) of the session object

Method specifies the values of the fields to be queried, and you can also restrict the data to be queried through the all(), first() cascading methods

Def query(self): """ """ # self.session.query(People).all() # self.session.query(People).all() # self.session.query(People. People.age).all() print(result)Copy the code

Of course, you can also use filter_BY (condition) to filter by condition

Query (People).filter_by(name='xag').first() print(resp)Copy the code

3, update,

Update operations are generally performed as follows:

  • Query Displays the object to be updated

  • Directly updates the data in the object

  • The session object is used to commit the changes and complete the update operation

Def def (self, id): """ """ # temp_people = self.session.query(People).filter_by(id=id).first() # Self.session.com MIT ()Copy the code

Note that you can use the update() method for shorthand here

Def def (self, id): """ Self.session.query (People).filter(people.id == id).update({people.name: "xag", people.age: 1}) self.session.commit()Copy the code

4, delete,

The delete operation corresponds to the delete() method, which is also used to query, delete, and submit the session to complete the deletion operation

The following uses deleting a record based on the ID as an example:

Def del_by_id(self, id): """ def del_by_id(self, id): "" """ del_count = self.session.query(People).filter(people.id == id).delete() print(' delete number :', del_count) self.session.commit()Copy the code

5. The last

This article explains in detail two ways Python can operate Mysql by adding, deleting, and querying a table

In real projects, PyMysql is recommended for simple crawlers or automation. Otherwise, it is recommended to go directly to SQLAlchemy, which is more powerful and convenient

I have uploaded all the source code to the background, follow the public number “AirPython” reply “dball” can get all the source code

If you think the article is good, please like, share, leave a message, because this will be my strongest power to continue to output more high-quality articles!