MySQLdb使用指南

最新推荐文章于 2025-06-24 09:18:10 发布

coderate

最新推荐文章于 2025-06-24 09:18:10 发布

阅读量484

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： MySQLdb CRUD Mysql Sql

本文链接：https://blog.youkuaiyun.com/leel0330/article/details/79960042

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文是一篇关于MySQLdb的使用教程，涵盖了从创建数据库连接、获取cursor到执行CRUD操作，包括创建表、插入数据、查询、添加列和索引，以及更新数据的详细步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0. 引入MySQLdb

import MySQLdb

1. 创建mysql连接

db = MySQLdb.Connect(host='localhost', port=3306,
                     user='root', passwd='root',
                     db = 'test')

参数根据你的配置来，这里以localhost为例。

2. 获取cursor

cursor = db.cursor()

3. 创建表

drop_table_sql = 'drop table if exists User'

cursor.execute(drop_table_sql)

create_sql = """
create table if not exists User(
    uid int not null primary key auto_increment,
    username varchar(30) not null,
    age int not null,
    gender int not null default -1,
    createtime date not null,
    key username_idx(username),
    key createtime_idx(createtime)
)
"""

print create_sql

print cursor.execute(create_sql)

4. 插入数据

insert_data_sql = 'insert into User(username, age, gender,
     createtime)values(%s, %s, %s, %s)'

print insert_data_sql

data = [
    ("\'Tom\'", 20, 1, '\'2017-10-4\''),
    ("\'Jack\'", 40, 1, '\'2016-3-9\''),
    ("\'Rose\'", 30, 2, '\'2018-4-23\''),
    ("\'Jane\'", 23, 2, '\'2015-12-30\''),
]

for line in data:
    insert_sql = insert_data_sql % 
        (line[0], line[1], line[2], line[3])
    print insert_sql
    cursor.execute(insert_sql)

5. 查询数据

query_sql = 'select username, age, gender, createdate from User 
    order by createdate desc'

query_cnt = cursor.execute(query_sql)

# fetch all
# if query_cnt > 0:
#     data = cursor.fetchall()
#     for row in data:
#         print row

# fetch many
# if query_cnt > 0:
#     data_1 = cursor.fetchmany(2)
#     for line in data_1:
#         print 'data 1:', line
#     data_2 = cursor.fetchmany(2)
#     for line in data_2:
#         print 'data 2:', line

# fetch one
if query_cnt > 0:
    row = cursor.fetchone()
    while row:
        print 'row: ', row
        row = cursor.fetchone()

6. 表添加新列

虽然这里演示如果在表建好后添加新的字段，但是现实中，尽可能在前期将表结构设计好，避免库表中存有大量数据后新增字段。

alter_col_sql = 'alter table User add 
    userdesc varchar(100) not null default "simple desc"'

print alter_col_sql

cursor.execute(alter_col_sql)

7. 表添加新的索引

alter_index_sql = 'alter table User 
    add index name_age_createdate_idx(username, age, createdate)'

print alter_index_sql

cursor.execute(alter_index_sql)

8. 修改数据


update_sql = 'update User set userdesc = "hello mysql" 
    where username = "Jack"'

print update_sql

cursor.execute(update_sql)