入门（九）操作mysql

1、连接数据库

sql

create database python charset utf8;
use python;

create table t_user (
  id int primary key auto_increment,
  username varchar(200) unique not null,
  password varchar(200),
  age int,
  height double
)engine=Innodb charset utf8;

python连接

import pymysql

# 定义数据库连接信息

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'egg',
    'charset': 'utf8'
}
# 获取连接
conn = pymysql.connect(**config)

print(conn)

2、获取游标执行sql关闭资源

import pymysql

# 定义数据库连接信息

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'egg',
    'charset': 'utf8'
}
# 获取连接
conn = pymysql.connect(**config)

# 获取游标
cursor = conn.cursor()

try:
    cursor.execute('select * from t_user')
except pymysql.err.ProgrammingError:
    cresql = '''
    create table t_user (
      id int primary key auto_increment,
      username varchar(200) unique not null,
      password varchar(200),
      age int,
      height double
    )engine=Innodb charset utf8;
    '''
    cursor.execute(cresql)
# 执行sql
sql = '''
    insert into t_user
    (username,password,age,height)
    values
    ('tom','123',21,180.5)
'''
num = cursor.execute(sql)
print(num)

# 提交事务
conn.commit()

# 关闭资源
cursor.close()
conn.close()

动态sql

sql = '''
    insert into t_user
    (username,password,age,height)
    values
    (%s,%s,%s,%s)
'''
num = cursor.execute(sql,['tom','123',21,180.5])

3、执行查询语句

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'egg',
    'charset': 'utf8'
}
# 获取连接
conn = pymysql.connect(**config)

# 获取游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = '''
    select * from t_user
'''
cursor.execute(sql)
print(cursor.fetchone())  # 返回一条数据，默认返回的是元组，配置DictCursor后返回的是字典

# print(cursor.fetchmany(3))  # 返回指定条数的集合
# print(cursor.fetchall()) # 返回所有数据


# 关闭资源
cursor.close()
conn.close()