python interacts with database mysql

pip install pymysql

Import pymysql Library

import pymysql

2. Link to the database. Here we use a dictionary to receive it.

db_conf = { 'host':'ip', # Host ip 'port':3306, # mysql default port 'user':'User name', # mysql database user name 'password':'Password', # mysql database password 'db':'Database name', # Create or existing databases 'charset':'utf8' # Coding, note that this is not utf-8. }

3. Create cursor objects

conn = pymysql.connect(**db_conf) # Here ** db_conf unpacks, equivalent to key=values key-value pairs corsor = conn.cursor()

4. Execute sql statements to get results
Here you need to use the try statement to catch exceptions

try: sql = "select * from test" # Create sql statements and assign variables sql rv = corsor.execute(sql) # execute() method receives the number of rows returned by the executed sql statement resp = corsor.fetchall() #fetchall() method receives the content returned by executing sql statements [print(i) for i in resp] # List derivation resp is a variable that can be iterated through object loops and printed out except Exception as e: print(e) conn.rollback() # It's equivalent to revocation, rollback if it fails

5. Submit the transaction and close the connection

finally: conn.commit() # Submit and close the transaction corsor.close() # Close cursor conn.close() # Close connection

# 1. Import oymysql library first import pymysql # 2. Establishing links with databases db_conf = { 'host':'192.168.61.129', # Host ip 'port':3306, # mysql default port 'user':'admin', # User name 'password':'Root110qwe', # Password 'db':'student', # Database name 'charset':'utf8' # Coding, note that this is not utf-8. } #3. Create cursor objects conn = pymysql.connect(**db_conf) # Here ** db_conf unpacks, equivalent to key=values key-value pairs corsor = conn.cursor() #4. Execute sql statements to get results # Here you need to use the try statement to catch exceptions try: sql = "select * from test" # Create sql statements and assign variables sql rv = corsor.execute(sql) # execute() method receives the number of rows returned by the executed sql statement resp = corsor.fetchall() #fetchall() method receives the content returned by executing sql statements [print(i) for i in resp] # List derivation resp is a variable that can be iterated through object loops and printed out except Exception as e: print(e) conn.rollback() # It's equivalent to revocation, rollback if it fails finally: conn.commit() # Submit and close the transaction corsor.close() # Close cursor conn.close() # Close connection

(1, 'Nanfeng', 18) (2, 'Xizhou', 20)

Queries exist in those databases

Switch to student database

Look at those tables

There's only our test table, so let's query the data.

With statements like desc table names, you can view the table structure

2 October 2019, 09:46 | Views: 4226