python interacts with database mysql

Operate mysql through pycharm's concise code block to help us understand

The concrete steps can be divided into five steps.

1. Install and install the pymysql Library

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

Overall code block

# 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

Output result

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

We can go to the database to find out if the data is like this.

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

Tags: SQL Database MySQL Pycharm

Posted on Wed, 02 Oct 2019 09:46:37 -0400 by Tomatron