Python Sharing Simple Steps for Operating MySQL Database

Preface

Nowadays, Python is more and more used by the public, especially in the era of AI artificial intelligence, which requires more efficient programming, so Python often becomes an important voice of artificial intelligence and big data programming. Since it is a programming language, it is more or less necessary to operate on data. In this article, we will take you to use python to operate mysql.

If nothing else, just go to the code

MySQL table building

When building the table, I encountered 1 pits, which were not solved, such as modifying the default engine of MySQL. default-storage-engine=InnoDB; Execute error reporting. . . Helpless

use mybatistable;
drop table Test;
-- INNODB   Support transactions  
-- Mysql  The default engine is  MyISAM , Transactional operations are not supported 
--  In creating  mysql  Table, it is best to specify the engine used by the table  
--  Or modify it directly Mysql  The default database engine is  InnoDB
-- default-storage-engine=InnoDB;  Execute error reporting   . . . Helpless 


create table Test(
	 id int(10)  not null auto_increment,
	 name varchar(20)  not null,
	 password varchar(30) not null,
	 constraint pk_id primary key(id),
	 constraint uk_name unique(name)
)engine=InnoDB charset=utf8;
--  Engine for viewing tables 
show create table Test;
--  Engine for updating tables   Execute error reporting 
-- alter table Test type = InnoDB; 

insert into Test values(default,' Xiao Hong ',123);
insert into Test values(default,' Xiao Li ',123);
insert into Test values(default,' Xiao Zhao ',123);
insert into Test values(default,' Small Army ',123);
insert into Test values(default,' Small square ',123);

select * from  Test;

python Operation MySQL

import pymysql

'''
     Connect  mysql  Steps of database 
    fetchall  Accept all returned result rows 
    PS: Only  innodb  You can set the table of type  autocommit;
'''
def connectMySql():
    host =  '127.0.0.1'
    username = 'root'
    password = 'root'
   # dbName = 'MyBatistable'
    #  Get the database connection object 
    conn = pymysql.connect(host,username,password)
    # Turn off automatic commit transactions for the database 
    conn.autocommit(False)
    #  Select the database to operate on 
    conn.select_db('MyBatistable')  # Overwrite the database name of the previous operation 
    #  Get the cursor 
    cursor = conn.cursor()
    # Definition  SQL  Statement 
    sql = 'select * from Test'
    sql1 = 'insert into test values(default," Small pot ","120")'
    sql2 = 'update test set name=" Small library 2" where id = 2'
    sql3 = 'delete from test where id = 2'
    # Execute  SQL  Statement 
   # row = cursor._query(sql)
    # Execute  execute  Method to return the number of rows affected 
    row = cursor.execute(sql1)
    print('row type:',type(row))
    print(' The number of rows affected is :',row)
    if row > 0:
        conn.commit() #  Commit transaction 
        print('SUCCESS')
    else:
        conn.rollback() #  Rollback transaction 
        print('Failure')
    # Use DQL  Returns the result set , In the form of tuples 
    nums = cursor.fetchall()
    print('nums Type:',type(nums))
    # Processing result sets 
    if nums != () :
        for  num in  nums:
            print('--',num)

if __name__ == '__main__':
    connectMySql()

Summarize

When Python operates MySQL, transactional operations are not supported because MySQL uses the MyISAM engine by default. In Python operation Mysql, the automatic submission transaction was turned off, and it was found that it was useless. Then Baidu said on the Internet that InnoDB in Mysql supported transactions, and then I found that the engine of my own table was MyISAM, and I wanted to cry without tears. Then I started building tables and testing again.