【python】【初学笔记八】数据库

"""
#sqlist
import sqlite3  # 导入访问SQLite的模块
# ①、打开或创建数据库
# 也可以使用特殊名：:memory:代表创建内存中的数据库
conn = sqlite3.connect('first.db')
# ②、获取游标
c = conn.cursor()
# ③、执行DDL语句创建数据表
c.execute('''create table user_tb(
    _id integer primary key autoincrement,
    name text,
    pass text,
    gender text)''')
# 执行DDL语句创建数据表
c.execute('''create table order_tb(
    _id integer primary key autoincrement,
    item_name text,
    item_price real,
    item_number real,
    user_id inteter,
    foreign key(user_id) references user_tb(_id) )''')
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()
"""

#mysql
# pip show mysql-connector-python
# pip install mysql-connector-python
# pip uninstall mysql-connector-python


import mysql.connector
print(mysql.connector.apilevel)
print(mysql.connector.paramstyle)
#创建
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、执行DDL语句创建数据表
c.execute('''create table user_tb(
    user_id int primary key auto_increment,
    name varchar(255),
    pass varchar(255),
    gender varchar(255))''')
# 执行DDL语句创建数据表
c.execute('''create table order_tb(
    order_id integer primary key auto_increment,
    item_name varchar(255),
    item_price double,
    item_number double,
    user_id int,
    foreign key(user_id) references user_tb(user_id) )''')
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

#插入数据
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用执行insert语句插入数据
c.execute('insert into user_tb values(null, %s, %s, %s)',
    ('wukong', '123456', 'male'))
c.execute('insert into order_tb values(null, %s, %s, %s, %s)',
    ('maose', '34.2', '3', 1))
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

#插入多个数据
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用executemany()方法把同一条SQL语句执行多次
c.executemany('insert into user_tb values(null, %s, %s, %s)',
    (('sun', '123456', 'male'),
    ('bai', '123456', 'female'),
    ('zhu', '123456', 'male'),
    ('niu', '123456', 'male'),
    ('tang', '123456', 'male')))
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

#修改数据
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用executemany()方法把同一条SQL语句执行多次
c.executemany('update user_tb set name=%s where user_id=%s',
    (('xiaosun', 2),
    ('xiaobai', 4)))
# 通过rowcount获取被修改的记录条数
print('修改的记录条数：', c.rowcount)
conn.commit()
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

#查询
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用执行select语句查询数据
c.execute('select * from user_tb where user_id > %s', (2,))
print('查询返回的记录数:', c.rowcount)
# 通过游标的description属性获取列信息
for col in (c.description):
    print(col[0], end='\t')
print('\n--------------------------------')
# 直接使用for循环来遍历游标中的结果集
for row in c:
    print(row)
    print(row[1] + '-->' + row[2])
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

#调用procedure
# ①、连接数据库
conn = conn = mysql.connector.connect(user='root', password='password',
    host='localhost', port='3306',
    database='python1', use_unicode=True)
# ②、获取游标
c = conn.cursor()
# ③、调用callproc()方法执行存储过程
# 虽然add_pro存储过程需要3个参数，但最后一个参数是传出参数，
# 因此程序不会用它的值
result_args = c.callproc('add_pro', (5, 6,0))
# 返回的result_args既包含了传入参数的值，也包含了传出参数的值
print(result_args)
# 如果只想访问传出参数的值，可直接访问result_args的第3个元素，如下代码
print(result_args[2])
# ④、关闭游标
c.close()
# ⑤、关闭连接
conn.close()

# pip install PyMySQL
import pymysql
# 导入访问pymysql模块
import pymysql
db = pymysql.connect("localhost","root","32147","python")
#使用cursor() 方法创建一个游标对象cursor
cursor = db.cursor()
#使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")
#使用 fetchone() 方法获取单条数据
data = cursor.fetchone()
print("Database version: %s" % data)
#关闭数据库连接
db.close()