Python连接MySQL

一、连接数据库方法

import pymysql

db = pymysql.connect(
    host = '127.0.0.1',
    user = 'root',
    password = '123',
    port = 3306
)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute('CREATE DATABASE spiders ')   #创建名为spiders的数据库
db.close()

#输出结果：Database version: ('8.0.13',)

若能够正常输出对应的数据库版本号，则证明已经成功连接了数据库。

二、创建表

import pymysql

db = pymysql.connect(
    host = '127.0.0.1',
    user = 'root',
    password = '123',
    port = 3306,
    db = 'spiders'
)
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT  EXISTS users (id VARCHAR(255) NOT NULL ,name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cursor.execute(sql)
db.close()

执行结果：

三、插入数据

import pymysql

id = '520'
name = 'aa'
age = 32
db = pymysql.connect(
    host = '127.0.0.1',
    user = 'root',
    password = '123',
    port = 3306,
    db = 'spiders'
)
cursor = db.cursor()
sql = 'INSERT INTO users(id,name, age) values(%s, %s, %s)'
# cursor.execute(sql, (id, name, age))
try:
    cursor.execute(sql, (id, name, age))
    print('插入成功啦')
    db.commit()
except:
    print('啊哦，插入失败了')
    db.rollback()
db.close()

#输出结果：插入成功啦

执行结果：

四、更新数据

import pymysql

db = pymysql.connect(
    host = '127.0.0.1',
    user = 'root',
    password = '123',
    port = 3306,
    db = 'spiders'
)
cursor = db.cursor()
sql = 'UPDATE users SET name = %s WHERE name = %s'
try:
    cursor.execute(sql, ('VAE', 'aa'))
    print('更新成功啦')
    db.commit()
except:
    print('啊哦，更新失败了')
    db.rollback()
db.close()

#输出结果：更新成功啦

执行结果：

五、删除数据

import pymysql

db = pymysql.connect(
    host = '127.0.0.1',
    user = 'root',
    password = '123',
    port = 3306,
    db = 'spiders'
)
cursor = db.cursor()
sql = 'DELETE FROM users WHERE id=520 '
try:
    cursor.execute(sql,)
    print('删除成功啦')
    db.commit()
except:
    print('啊哦，删除失败了')
    db.rollback()
db.close()

#输出结果：删除成功啦