Shulou(Shulou.com)06/02 Report--

This article mainly introduces how python through pymysql to achieve the operation of the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone reading this python how to achieve the operation of the pymysql will be harvested, let's take a look at it.

Python provides many methods for operating mysql databases, among which pymysql is a common and easy-to-use third-party database operation library. Pymysql can be easily implemented for common database additions, deletions and queries.

1. Install pymysql: pip install pymysql (executed in a command line window)

2. uninstall pymysql: pip uninstall pymysql (executed in a command line window)

database connection

Note that port is not enclosed in quotes charset is utf 8, not utf-8.

#Get database connection object connection = pymysql.connect (host='localhost', port=3306, user='root', passwd='2732195202', db='book', charset ='utf8 ')#Get a cursor driver = connection.cursor()#Execute a sqlddriver.execute ("select version()")#Get the return value of executing sql resultData=driver.fetchall()print (resultData)#close database connection.close() create database table import pymysql#get database connection objectconnection = pymysql.connect (host='localhost', port=3306, user='root', passwd='2732195202', db='book', charset ='utf8 ')#Get a cursor driver=connection.cursor()#Delete driver.execute if the database exists ("drop table if exists t_emp ")#define sql statement sql="" CREATE TABLE `t_emp`( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary',`department` varchar(20) DEFAULT NULL COMMENT ' department',`salary` decimal(10,2) DEFAULT NULL COMMENT 'salary',`age` int(11) DEFAULT NULL COMMENT 'year',`sex` varchar(4) DEFAULT NULL COMMENT ' sex', PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8; """#execute sqldriver.execute(sql)#close data connection.close() Add data to database

1. What needs to be noted is the specification sql. The fields written in this specification are all written on, and the default corresponding is not used.

2. The object committing the transaction is a database connection object, not a cursor object

3.pycharmWhen connecting mysql data, if the connection driver is a higher version, you need to add the time zone, jdbc:mysql://localhost/book? serverTimezone=GMT%2B8

4. If the primary key is auto-incrementing, you cannot manually specify a value, you cannot write the field, and let it grow.

#Get database connection object connection=pymysql.connect(host ='localhost ',port=3306,user ='root',passwd ='2732195202 ',db ='book',charset ='utf8 ')#Get a cursor driver=connection.cursor()#Define sql statement sql="" insert into t_emp(name,department,salary,age,sex) values("tom","Dev", 8000,25,"M"), ("tom","Dev", 8000,25,"M") ""#Try to catch errors: #Execute SQL and return the number of affected rows result=driver.execute(sql) #Submit a transaction connection.commit() print("sql(insert)->error")except: #Roll back transactions if errors occur print("sql(insert)->error") driver.rollback()#close database connection.close() modify data in a table

Note: Before operating the database, you need to confirm whether the connection database is successfully obtained and the database is selected.

2. Uninstall third-party libraries: pip uninstall pymysql

#Get database connection object autocommit=True: Set database autocommit connection=pymysql.connect(host="localhost",port=3306,user='root', passwd=' 2732195202', db='book', charset=' utf8', autocommit=True)#Get cursor object driver=connection.cursor()#Define sql ="update t_emp set salary=%s,name=%s where id=%s;"#Perform rollback operation if sql error, submit try: sql successfully #Execute sql and return the number of rows affected result=driver.execute(sql,[6000,"admin",19]) connection.commit() print("sql（update）->success")except: print("sql(update)->error") connection.rollback()#Close database connection objectconnection.close() Query data

1. The.py file in the project cannot conflict with the file in the python library, otherwise an exception will occur.

#Get database connection object connection=pymysql.connect (host='localhost', port=3306,user=' root', passwd='2732195202', db=' book', charset='utf8')#Get a cursor object driver=connection.cursor()#Define sqlsql="select id, name, department, salary, age, sex from t_emp where id>%s and sex=%s"#Get once only, If you get multiple parameters, you will get null. If you have multiple parameters, you need to pass a tuple try: driver.execute(sql,(1,"female")) #Get all query results and return a tuple resultAll=driver.fetchall() print("resultAll:", resultAll) #Get 2 pieces of data resultTwo=driver.fetchmany(2) print("resultTwo:", resultTwo) #Get a piece of data resultOne=driver.fetchone() print("resultThree:", resultOne) print("sql(select)->success")except: connection.rollback() print("sql(select)->error")#Close database connection.close() Delete records from table import pymysql#Get database connection objectconnection = pymysql.connect(host ='localhost ', port=3306, user ='root', passwd ='2732195202 ', db ='book', charset ='utf8 ')#Get a cursordriver = connection.cursor()#Define sqlsql="delete from t_emp where id=%s"try: #Execute a SQL driver.execute(sql, (21)) #Submit a transaction connection.commit() print("sql(delete)->success")except Exception as e: #Roll back transactions connection.rollback() print("sql(delete)->error") print(e)#Close database connection.close() About "Python how to add, delete and change operations through pymysql" The content of this article is introduced here, thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how python can be added, deleted and checked through pymysql." If you still want to learn more knowledge, you are welcome to pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.