Python-MySql operation

Python-MySql operation

pymysql

GitHub address of pymysql: https://github.com/PyMySQL/PyMySQL

image.png

Common data types

Types of

Byte size

Signed range (Signed)

Unsigned range (Unsigned)

TINYINT

1

-128 ~ 127

0 ~ 255

SMALLINT

2

-32768 ~ 32767

0 ~ 65535

MEDIUMINT

3

-8388608 ~ 8388607

0 ~ 16777215

INT/INTEGER

4

-2147483648 ~2147483647

0 ~ 4294967295

BIGINT

8

-9223372036854775808 ~ 9223372036854775807

0 ~ 18446744073709551615

String

Types of

Byte size

Example

CHAR

0-255

Type: char(3) input'ab', it is actually stored as'ab', input'abcd' is actually stored as'abc'

VARCHAR

0-255

Type: varchar(3) input'ab', the actual storage is'ab', input'abcd', the actual storage is'abc'

TEXT

0-65535

Large text

Date and time type

Types of

Byte size

Example

DATE

4

'2020-01-01'

TIME

3

'12:29:59'

DATETIME

8

'2020-01-01 12:29:59'

YEAR

1

'2017'

TIMESTAMP

4

'1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC

Installation method

 python3 -m pip install PyMySQL

Connection

Create an object: call the connect() method to establish a connection with the database

conn=connect (parameter list)

Parameter host: the connected mysql host, if the local machine is'localhost' Parameter port: the port of the connected mysql host, the default is 3306 Parameter database: the name of the database parameter user: the user name of the connection parameter password: the password of the connection parameter charset: The coding method used in communication, it is recommended to use the utf8 object method close() to close the connection commit() to submit cursor() to return the Cursor object, which is used to execute the sql statement and obtain the result

Cursor

Used to execute SQL statements, the most frequently used statements are select, insert, update, delete to obtain the Cursor object: call the cursor() method of the Connection object

c=conn.cursor()

The object method close() closes execute(operation [, parameters]) to execute the statement and returns the number of rows affected. It is mainly used to execute insert, update, delete statements, and can also execute create, alter, drop and other statements fetchone() execution When querying, get the first row data of the query result set, and return a tuple. When executing the query, fetchall() gets all rows of the result set. One row forms a tuple, and then assembles these elements into a tuple to return

Connect to the database

import pymysql

conn = pymysql.connect(host='localhost', user='baxiang', password='123456', port=3306)
cursor = conn.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print(''.join(data))
cursor.execute('SHOW DATABASES')
data = cursor.fetchone()
print(''.join(data))
conn.close()

Create a database

import pymysql

conn = pymysql.connect(host='localhost', user='baxiang', password='123456', port=3306)
cursor = conn.cursor()
cursor.execute('CREATE DATABASE py_test DEFAULT CHARACTER SET utf8')
cursor.execute('SHOW DATABASES')
dbList = cursor.fetchall()
for db in dbList:
    print('db name:'+''.join(db))
conn.close()

fetchall gets all the rows of the result set, one row forms a tuple, and then assembles these elements into a tuple and returns

Create table

import pymysql

conn = pymysql.connect(host='localhost', user='baxiang', password='123456', port=3306, db='py_test')
cursor = conn.cursor()
sql ='''
CREATE TABLE IF NOT EXISTS exam(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
)COMMENT='Student'
'''
cursor.execute(sql)
cursor.close()
conn.close()

Insert data

import pymysql

conn = pymysql.connect(host='localhost', user='baxiang', password='123456', port=3306, db='py_test')
cursor = conn.cursor()
try:
    cursor.execute("INSERT INTO exam(id,name,score) VALUES (%s,%s,%s)", (2009100981,'ba', 95))
    conn.commit()
except:
    conn.rollback()

cursor.close()
conn.close()

Query data

import pymysql

conn = pymysql.connect(host='localhost', user='baxiang', password='123456', port=3306, db='py_test')
cursor = conn.cursor()
try:
    cursor.execute("SELECT * FROM exam WHERE score >= 60")
    dataList = cursor.fetchall()
    for data in dataList:
        print(data)
except Exception as e:
    print(e)

cursor.close()
conn.close()
Reference: https://cloud.tencent.com/developer/article/1437123 Python-MySql Operation-Cloud + Community-Tencent Cloud