python -【十一】pymysql 基础使用

安装 pymysql 三方依赖
pip install pymysql

from pymysql import Connection

conn = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='root'
)

# 获取游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db('test')
# 使用游标对象执行 SQL 语句
cursor.execute("""
CREATE TABLE `test_pymysql` (
  `id` int NOT NULL COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '名字',
  `age` int NOT NULL COMMENT '年龄',
  `gender` varchar(255) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='pymysql测试表'
""")
# 关闭链接
conn.close()

插入/查询数据

from pymysql import Connection

conn = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    # 开启自动提交，无需手动 commit 事务
    autocommit=True
)


def create_data() -> str:
    data: list = []
    for i in range(1, 10):
        data.append((f'100{i}', f'张{i}', f'{20 + i}', f'{i % 2}'))

    sql: str = ''
    for datum in data:
        sql += str(datum)
        sql += ','
    return sql.rstrip(',')


# 获取游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db('test')

print('============插入数据===========')
suffix = create_data()
all_sql = 'insert into test_pymysql values ' + suffix
print(f'插入sql：{all_sql}')
cursor.execute(all_sql)
# commit 或者是设置连接中参数：autocommit = True
# conn.commit()

print('============查询数据===========')
cursor.execute('select * from test_pymysql')
results = cursor.fetchall()  # type: tuple
for result in results:
    print(result)

# 关闭链接
conn.close()