Python3 connecting the MySQL database mysql-client

Installing Mysqlclient

To enable Python to operate MySQL requires the MYSQLDB driver, which is a necessary module for Python to operate MySQL.

Install with PIP

install mysqlclient

Installation Error

Download Mysqlclient installation package at this site: https://www.lfd.uci.edu/~gohlke/pythonlibs/# for local installation

Here are all the versions of Mysqlclient retrieved from this site

CP37 represents the python3.7 version, Win32 represents a 32-bit system, so you need to choose the correct, otherwise the installation process will error platform mismatch.

Run command at the command line into the path where the installation files are located: Pip install MYSQLCLIENT?1.3.13?CP37?CP37M?WIN_AMD64.WHL (full file download)

Test

The test is very simple and checks whether the MySQLdb module can be imported properly.

>>> import MySQLdb

No error notification MYSQLDB module not found, instructions to install OK

Python Operations MySQL Database basics

#coding =utf-8import MySQLdbThe #connect () method is used to create a connection to a database where parameters can be specified: User name, password, host, and so on.#这只是连接到了数据库, you need to create a cursor to manipulate the database. conn= MySQLdb.connect (host=' localhost ', port =3306, user=' Root ', passwd=' 123456 ', db =' Test ',)#通过获取到的数据库连接conn下的cursor () method to create a cursor. cur = conn.cursor ()#创建数据表, the Execute () method through the cursor cur operation can write to a pure SQL statement. Manipulate the data using the Execute () method written as SQL statement Cur.execute ("CREATE TABLE student (ID int, name varchar (), class varchar (), age varchar ())#插入一条数据cur. Execute ("INSERT into student values (' 2 ', ' Tom ', ' 3 Year 2 class ', ' 9 ')")# Modify the query criteria data cur.execute ("update student set class= ' 3 Year 1 class ' WHERE name = ' Tom '")#删除查询条件的数据cur. Execute (" Delete from student where age= ' 9 ' ")#cur. Close () to close the cursor cur.close ()#conn. The commit () method commits the thing and must have this method when inserting a piece of data into the database. Otherwise the data is not actually inserted. Conn.commit ()#conn. Close () Closes the database connection conn.close ()          

Inserting data

It is not convenient to insert data by writing a pure SQL statement in the Execute () method above. Such as:

cur.execute("insert into student values(‘2‘,‘Tom‘,‘3 year 2 class‘,‘9‘)")

• 1

If I want to insert new data, I have to modify the value in this statement. We can make the following changes:

 #coding =utf-8import mysqldbconn= MySQLdb.connect (Host= ' localhost ', port = 3 306, User= ' 123456 ', db = ' test ',") cur = conn.cursor ()  #插入一条数据sqli = "INSERT INTO student VALUES (%s,%s,%s,%s) "Cur.execute (Sqli, ( ' 3 ',  ' Huhu ',  ' 2 year 1 class ',  ' 7 ') cur. close () Conn.commit () Conn. close ()             

What if you want to insert more than one value into a data table at a time?

#coding =utf-8import mysqldbconn= MySQLdb.Connect (host=' localhost ', port =3306, user=' Root ', passwd=' 123456 ', db = ' test ', "cur = conn.cursor ()  #一次插入多条记录sqli =  "INSERT into student values (%s, %s,%s,%s) "Cur.executemany (sqli,[(  ' 3 ',  ' Tom ',  ' 1 year 1 class ',  ' 6 '), ( ' 3 ',  ' Jack ',  ' 2 year 1 class ',  ' 7 '), ( ' 3 ',  ' Yaheng ',  ' 2 Year 2 class ',  ' 7 '),]) cur.< Span class= "Hljs-keyword" >close () Conn.commit () Conn. close ()             

executemany()The method can insert multiple values at a time, execute a single-heads SQL statement, but repeatedly execute parameters in the parameter list, with the returned value being the number of rows affected.

Python3 connecting the MySQL database mysql-client