Python featured-mysql database (python3._+ pymysql)

Previously wrote a blog using the MySQL database in Python, mainly using the Python2 and mysqldb drivers.

Python uses MySQL database

• Python2---> Python3
• MySQLdb-Pymysql

one , install Pymysql

Python is a programming language,MySQL is a database, they are two different technologies, to enable Python to operate the MySQL database requires the use of drivers. The Pymysql drive is selected here . :

Https://pypi.python.org/pypi/PyMySQL

Https://github.com/PyMySQL/PyMySQL

Of course, the simplest way to install it is to use the PIP command.

> Pip Install Pymysql

Replace the install with the show command to see if the Pymysql installation was successful.

Two, create MySQL table

Execute the following SQL statement to create a users table.

CREATETABLE' Users ' (' ID 'INT (11)NotNullauto_increment, ' email 'varchar (255) COLLATE utf8_bin not nullvarchar (255) COLLATE utf8_bin not nullprimary key (' id ')) engine=innodb default charset=utf8 COLLATE =utf8_binauto_increment =1;          

Three, Python operation MySQL

The next point is the Python operation MySQL database.

4.1 inserting data:

ImportPymysql.cursors#Connect MySQL Database connection = pymysql.connect (host=‘127.0.0.1', port=3306, user=‘Root', password=‘198876 ", Db=" guest ", 
 Charset=" utf8mb4", Cursorclass= Pymysql.cursors.DictCursor) # creating cursors from cursor cursor = Connection.cursor () # Create SQL statement, and Execute sql =  "insert into ' users ' (' email ', ' password ') VALUES ( ' [email protected] ', ' 123456 ')  "cursor.execute ( SQL) # commit sqlconnection.commit ()     

Regardless of the tool or library you are using, connecting to the database is essential. The host IP address of the database ,Port is the default port number for MySQL, user name for the data,password is the login password of the database, DB is the name of the database.

The cursor () method creates a database cursor.

Execute () method executes the SQL statement.

Commit () actually commits the operation of the database to the data.

4.2. Querying Data

ImportPymysql.cursors#Connect MySQL Database connection = pymysql.connect (host=‘127.0.0.1', port=3306, user=‘Root', password=‘198876', db=‘Guest', charset=‘Utf8mb4', cursorclass=Pymysql.cursors.DictCursor)#To create a cursor through the cursor cursor =Connection.cursor ()#Execute data Query sql ="SELECT ' id ', ' password ' from ' users ' WHERE ' email ' = ' [email protected] '"Cursor.execute (SQL)#Querying database single Data result =Cursor.fetchone ()Print(Result)print ( "----------- Ornate split Line------------ ") #< Span style= "COLOR: #008000" > execute data query sql =  "select ' id ', ' Password ' from ' users '  "cursor.execute (SQL) # query database multiple data result = Cursor.fetchall () Span style= "COLOR: #0000ff" >for data in result: print#  Close data connection connection.close ()          

the next operation is the database query.

Fetchone () is used to query a single piece of data.

Fetchall () is used to query more than one piece of data.

Close () don't forget to close the data connection at the end.

Operation Result:

{‘Password‘:‘123456‘,'ID': 1}-----------ornate Split line------------{'password': '123456', ' ID': 1} {'password': '654321', 'ID': 2}   

Python feature-mysql database (python3._+ pymysql)