MySQL Connection module

MySQL Connection module

What is Pymysql?
Pymysql is a library used in the python3.x version to connect to the MySQL server, and MySQLdb is used in Python2.

Pymysql Installation
Pip Install Pymysql

Database connection
Before you connect to a database, verify the following:
The database has been created TESTDB
Mysql> desc Stu;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| ID | Int (100) | NO | PRI | NULL | auto_increment |
| name | varchar (100) | NO | | NULL | |
| Age | Int (100) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+

#打开数据库连接
conn = Pymysql.connect (host= "192.168.1.48", port=3306, user= "root", passwd= "123456", db= "TESTDB")

#使用cursor () method gets the operation cursor
cus = Conn.cursor ()

sql = "SELECT * from Stu;"

#使用execute方法执行SQL语句
Cus.execute (SQL)

#使用 Fetchone () method to get a single piece of data
result = Cus.fetchall ()
Print (Result)

#关闭游标连接
Cus.close ()

#关闭数据库连接
Conn.close ()

Execute the above script output as follows:
((1, ' a ', "), (2, ' B ', 13))

MySQL Things

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

Transactions are supported in MySQL only for databases or tables that use the INNODB database engine
Transactions can be used to maintain the integrity of the database, to ensure that a batch of SQL statements are either all executed or not executed
Transactions are used to manage insert,update,delete statements

Prohibit Auto-commit:

Mysql> Show variables like "%autocommit%";
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit | On |
+---------------+-------+
1 row in Set (0.00 sec)

Mysql> SET autocommit=0;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like "%autocommit%";
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in Set (0.00 sec)

When a heap of INSERT statements is executed, a commit is required to display the data, or there is no insert success

Import Pymysql

conn = Pymysql.connect (host= "192.168.1.48", port=3306, user= "root", passwd= "123456", db= "TESTDB")
cus = Conn.cursor ()
Sql_insert = "INSERT into Stu (name,age) VALUES (' d ', 15);"
Cus.execute (Sql_insert)
Cus.execute (' Commit ')
Sql_select= "SELECT * from Stu;"
Cus.execute (Sql_select)
#获取第3条数据
#reject =cus.fetchmany (3)
#获取第一条数据
#reject =cus.fetchone ()
#查看所有数据
Reject=cus.fetchall ()

Print (Reject)
#conn. Close ()

Example: by writing a class, the benefit is that the database is called only once

Import Pymysql

#定义一个类, the constructor is written in a dictionary form
Class Testmysql (object):
def Init(self):
Self.dbconfig = {
"Host": "192.168.1.48",
"Port": 3306,
"User": "Root",
"passwd": "123456",
"DB": "TESTDB"
}
#多值传入, *karges dictionary args: tuples
conn = Pymysql.connect (**self.dbconfig)
Self.cus = Conn.cursor ()

def select(self):    sql_select=‘select * from stu;‘    self.cus.execute(sql_select)    reject=self.cus.fetchall()    self.cus.close()    print(reject)def update(self):    sql_update="update stu set name=‘e‘ where id=24;"    self.cus.execute(sql_update)    self.cus.execute(‘commit;‘)

If name = = 'main':
conn = Testmysql ()
Conn.update ()
Conn.select ()

MySQL Connection module