Python 访问数据库


访问mysql数据库要先安装驱动程序,运行pip install PyMySQL命令安装PyMySQL。

查询数据库

下面是创建表的SQL:

create table user(
    id bigint primary key AUTO_INCREMENT,
    name varchar(50),
    password varchar(100),
    status int,
    descr varchar(200)
);
alter table user AUTO_INCREMENT=1;

下面是往表中增加数据的SQL:

insert into user(name, password, status, descr) values('tom', '123', 1, 'like game');
insert into user(name, password, status, descr) values('james', 'abc', 1, 'love music');

下面是查询数据库的例子:

#!/usr/bin/python

import pymysql
import pymysql.cursors

conn = pymysql.connect(host='localhost',
                             user='tutor',
                             password='tutor',
                             db='tutor',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with conn.cursor() as cursor:
        # 查询-条
        sql = 'select * from user where name=%s'
        cursor.execute(sql, ('tom',))
        result = cursor.fetchone()
        print('name :', result['name'])

        # 查询多条
        sql = 'select * from user'
        cursor.execute(sql)
        result = cursor.fetchmany(size=10)
        for row in result:
            print(row)

finally:
    conn.close()

更改数据库

下面是更改数据库的例子:

#!/usr/bin/python

import pymysql.cursors

conn = pymysql.connect(host='localhost',
                       user='tutor',
                       password='tutor',
                       db='tutor',
                       charset='utf8mb4',
                       cursorclass=pymysql.cursors.DictCursor)

try:
    with conn.cursor() as cursor:
        # 插入一条
        sql = 'insert into user (name, password) VALUES (%s, %s)'
        cursor.execute(sql, ('rabbit', '1'))
        
        # 插入多条
        cursor.executemany(sql,[
                              ('dog', 1),
                              ('cat', 1),
                              ])
        conn.commit()

        sql = 'select * from user'
        cursor.execute(sql)
        result = cursor.fetchmany(10)
        print(result)
finally:
    conn.close()