44 - 操作MySQL数据库

最新推荐文章于 2023-08-23 11:27:26 发布

若尘

最新推荐文章于 2023-08-23 11:27:26 发布

阅读量1.2k

点赞数 2

分类专栏： Python面试100讲文章标签：操作MySQL数据库创建数据库及数据表向表中插入数据查询数据表 python面试题

本文链接：https://blog.csdn.net/qq_29339467/article/details/104613841

版权

Python面试100讲专栏收录该内容

100 篇文章 14 订阅

订阅专栏

1. 如何创建MySQL数据表
2. 如何向MySQL表中插入数据
3. 如何查询MySQL中的数据

1. 如何创建MySQL数据表

'''
pymysql
pip install pymysql
'''

from pymysql import *

def connectDB():
    db = connect('127.0.0.1', 'root', 'root', 'db', charset='utf8')
    return db

db = connectDB()
print(type(db))

def createTable(db):
    cursor = db.cursor()
    try:
        cursor.execute('''create table persons
                  (id int primary key not null, 
                   name text not null,
                   age int not null,
                   address char(100),
                   salary real);''')
        db.commit()
        return True
    except:
        db.rollback()
    return False
    
if createTable(db):
    print('create table success')
else:
    print('create table failed')

<class 'pymysql.connections.Connection'>
create table success

2. 如何向MySQL表中插入数据

def insertRecords(db):
    cursor = db.cursor()
    try:
        cursor.execute('delete from persons')
        cursor.execute('''
                    insert into persons(id, name, age, address, salary)
                    values(1, 'Bill', 32, 'Colifornia', 20000);''')
        cursor.execute('''
                    insert into persons(id, name, age, address, salary)
                    values(2, 'Mike', 30, 'China', 10000);''')
        cursor.execute('''
                    insert into persons(id, name, age, address, salary)
                    values(3, 'Jhon', 12, 'Norway', 30000);''')
        db.commit()
        return True
    except Exception as e:
        print(e)
        db.rollback()
    return False

if insertRecords(db):
    print('insert success')
else:
    print('insert failed')

insert success

3. 如何查询MySQL中的数据

import json

def selectRecords(db):
    cursor = db.cursor()
    sql = 'select name, age, salary from persons order by age desc'
    cursor.execute(sql)
    results = cursor.fetchall()
    print(type(results))
    fields = ['name', 'age', 'salary']
    records = []
    for row in results:
        records.append(dict(zip(fields, row)))
    return json.dumps(records)
    
print(selectRecords(db))
db.close()

<class 'tuple'>
[{"name": "Bill", "age": 32, "salary": 20000.0}, {"name": "Mike", "age": 30, "salary": 10000.0}, {"name": "Jhon", "age": 12, "salary": 30000.0}]

持续更新中。。。。