mysql

mysql

import pymysql
1.连接数据库

conn = pymysql.connect(host='localhost',
                       user='root',
                       password='redhat',
                       db='westos',
                       charset='utf8'
)

2.创建游标对象

cur = conn.cursor()

3.对数据库操作

######创建数据表#############
try:
    create_sqli = 'create table hello (id int,name varchar(30));'
    cur.execute(create_sqli)
except Exception as e:
    print('创建数据表失败:',e)
else:
    print('创建数据成功')

插入多条数据

try:
    info = [(i,i) for i in range(100,1000)]
    # 第一种方式
    # inser_sqli = "insert into hello values(%d,'%s');"
    # for item in info:
    #     print('insert语句：',inser_sqli %item)
    #     cur.execute(inser_sqli %item)
    # 第二种方式
    inser_sqli = "insert into hello values('%s','%s');"
    cur.executemany(inser_sqli,info)
except Exception as e:
    print('插入多条数据失败:',e)
else:
    print('插入多条数据成功')

查询数据库

sqli = ‘select * from hello’
默认不返回查询结果集 返回的是数据记录数

result = cur.execute(sqli)
print(result)
a= cur.fetchone()
print(a)

获取下一条查询结果集

print(cur.fetchone())

== 获取指定个数查询结果集==

print(cur.fetchmany(4))
info = cur.fetchall()
print(info)

print(cur.fetchmany(3))
# print('移动到指针最开始的地方...')
# cur.scroll(0,'absolute')
# print(cur.fetchmany(3))
# print(cur.fetchmany(2))
# print(cur.fetchall())
cur.scroll(-2,mode='relative')
print(cur.fetchmany(2))

练习

import random
from random import choice as choice
import string
import pymysql
# 生成指定位数密码, 前 n-1 位为数字, 最后一位为密码 ;
def create_passwd(count=6):
    nums = random.sample(string.digits, count - 1)
    letters = random.sample(string.ascii_letters, 1)
    return "".join(nums + letters)
a = create_passwd()
print(a,type(a))
# 生成随机的姓名, 有两个或三个汉字组成 ;
def create_name():
    first = ['许', '张', '赵', '钱', '孙', '李', '朱', '杨']
    second = ['彬', '群', '宁', '盼', '龙', '欢', '丹']
    last = ['彬', '群', '宁', '盼', '龙', '欢', '丹', ' ' ]
    name = choice(first) + choice(second)+ choice(last)
    return name.rstrip()
def main():
     # 1.连接数据库 host user passwd charset
     conn = pymysql.connect(host='localhost',
                        user='root',
                        password='redhat',
                        db='westos',
                        charset='utf8'
     )

     # 2.创建游标对象
     cur = conn.cursor()
     n = int(input("生成数据数:"))
     # 往数据库表中插入 n 条随机数据 ;
     for i in range(n):
     # cur.execute('insert into userinfo values("user1", "123");')
         sqli = 'insert into userinfo values ("%s", "%s");' %(create_name(), create_passwd())
         cur.execute(sqli)
         # 提交数据,并关闭连接 ;
     conn.commit()
     cur.close()
main()