Python&MySQL

Introduction

1.1 database classification

Two MySQL

2.1 grammar

Overview of 2.1.1

2.1.2 add data

--Insert multiple data
 INSERT INTO 'table name' (` field name 1 ', ` field name 2...) VALUES
                 (value 1, value 2...),
                 .....
                 (value 1, value 2...);

2.1.3 delete data

 DELETE FROM 'table name'; -- delete table content
 DELETE FROM table name WHERE condition; -- delete record

2.1.4 change data

UPDATE table name` 
SET 'field name 1' = value 1, 'field name 2' = value 2...

2.2 MySQL DB module

2.2.1 connecting to the database

    def get_conn(self):
        try:
            self.conn = MySQLdb.connect(
                host='127.0.0.1',
                user='root',
                passwd='123456',
                db='news',
                port=3306,
                charset='utf8'
            )
        except MySQLdb.Error as e:
            print('Error:%s' % e)  # Output string% s

2.2.2 close database

    def close_conn(self):
        try:
            if self.conn:
                self.conn.close()
        except MySQLdb.Error as e:
            print('Error: %s' % e)

2.2.3 query data

Query a record

    def get_one(self):
        sql = 'SELECT * FROM `news` WHERE `type`=%s;'  # Preparing for SQL
        cursor = self.conn.cursor()  # Find cursor
        cursor.execute(sql, ('Real time news',))  # Execute SQL. Note: when there is only one element, you need to add,
        rest = cursor.fetchone()  # Get a result
        rest = dict(zip([k[0] for k in cursor.description], rest))  # Processing data
        cursor.close()  # Close cursor
        self.close_conn()  # Close connection
        return rest
	

Query multiple records

    def get_more(self):
        sql = 'SELECT * FROM `news` WHERE `view_count`=%s;'
        cursor = self.conn.cursor()
        cursor.execute(sql, (1,))
        rest = cursor.fetchall()  # Get all the results
        rest = [dict(zip([k[0] for k in cursor.description], row)) for row in rest] 
        cursor.close()
        self.close_conn()
        return rest

2.2.4 add data

	# Add a piece of data
    def add_one(self):
        try:
            sql=(
                "INSERT INTO `news` (`title`,`content`,`type`,`image`,`author`,`is_valid`) VALUES"
                "(%s,%s,%s,%s,%s,%s);"
            )
            cursor=self.conn.cursor()
            cursor.execute(sql,('News headlines','News content','News type','News path','Li Li',1))
            self.conn.commit()  # Submission of affairs
            cursor.close()
        except MySQLdb.Error as e:
            print('Error: %s' % e)
            self.conn.commit()  # If there is an error, the correct part will be submitted
            # self.conn.rollback()  # In the event of an error, none will be submitted
        self.close_conn()

Three ORM

3.1 introduction

Advantages:

• Hide the details of data access, improve the efficiency of development;
• It is easy to construct the solidified data structure

Inferiority:

• Reduce performance;
• As a transition between database and object, X-QL increases learning cost.
• Not suitable for complex queries

3.2 sqlalchemy framework

• Official documents of sqlalchemy

3.2.1 add data

    # Insert a record
    def add_one(self):
        new_obj = News(title='Journalism n', content='content')
        self.session.add(new_obj)
        self.session.commit()
        return new_obj

    # Insert multiple records
    def add_more(self):
        self.session.add_all([
            News(title='News 666', content='Content 3'),
            News(title='Journalism der', content='Content 3')
        ])
        self.session.commit()

3.2.2 delete data

    def delete_data(self):
        data = self.session.query(News).get()  # Delete a record. Delete multiple records and query multiple records by reference
        self.session.delete(data)
        self.session.commit()

3.2.3 change data

	# Modify a record
    def update_data_one(self, jk):
        data = self.session.query(News).get(jk)
        if data:
            data.is_valid = 1
            self.session.add(data)
            self.session.commit()
            return True
        else:
            print('The record does not exist and cannot be modified')
            return False

    # Modify multiple records
    def update_data_more(self):
        data_list = self.session.query(News).filter_by(title='News 3')
        if data_list:
            for item in data_list:
                item.is_valid = 1
                self.session.add(item) # Note: write add() in the loop
            self.session.commit()
            return True
        else:
            print('The record does not exist and cannot be modified')
            return False

3.2.4 query data

    # Query a record
    def get_one(self, jk):
        data = self.session.query(News).get(jk)
        # data=self.session.query(News).filter_by(id=4).first()
        if data:
            print('ID:{0} , content:{1}'.format(data.id, data.title))
            return True
        else:
            print('Record does not exist')
            return False

    # Query multiple records
    def get_more(self):
        data_list = self.session.query(News).filter_by(title='News 3')
        if data_list:
            print('The total records queried are:{0}'.format(data_list.count()) + 'strip')
            for item in data_list:
                print('ID:{0} , content:{1}'.format(item.id, item.title))
        else:
            print('The record does not exist')

Four Flask framework

• Flask document
• Flask Sqlalchemy document

Five Netease News demo

Netease News demo