Python笔记（20）数据库编程

安装MariaDB、修改编码格式

#安装数据库服务端：

yum install mariadb-server.x86_64 -y

　　　　　　...

Installed:
mariadb-server.x86_64 1:5.5.60-1.el7_5

Dependency Installed:
libaio.x86_64 0:0.3.109-13.el7
mariadb.x86_64 1:5.5.60-1.el7_5
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7
perl-DBD-MySQL.x86_64 0:4.023-6.el7
perl-DBI.x86_64 0:1.627-4.el7
perl-Data-Dumper.x86_64 0:2.145-3.el7
perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7
perl-PlRPC.noarch 0:0.2020-14.el7

Complete!

#开启服务

systemctl start mariadb

#设置数据库密码：

mysql_secure_installation

参考：https://blog.csdn.net/damys/article/details/50177331

为使数据库中的表支持中文，可以修改默认编码格式：

#临时修改utf8编码格式：
MariaDB [(none)]> set character_set_client='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set character_set_database='utf8';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set character_set_server='utf8';
Query OK, 0 rows affected (0.00 sec)
#永久修改utf8编码格式：
vim /etc/my.cnf.d/client.cnf

  7 [client]
  8     default-character-set=utf8
  9 # This group is not read by mysql client library,

vim /etc/my.cnf.d/server.cnf

 12 [mysqld]
 13     character-set-server=utf8
 14 # this is only for embedded server

systemctl restart mariadb.service
#查看变量并模糊查询char:

[root@centos01 python]# mysql -u root -p

Enter password:******

MariaDB [(none)]> show variables like '%char%';

Python调用MariaDB

#在数据库中创建一个users库，库中创建一个userinfo空表：

MariaDB [(none)]> create database users;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use users
Database changed
MariaDB [users]> create table userinfo (Name varchar(255),PassWord varchar(255));
Query OK, 0 rows affected (0.04 sec)

#安装接口软件：

[root@server ~]# yum search python|grep -i mysql           #-i表示不区分大小写
MySQL-python.x86_64 : An interface to MySQL
[root@server ~]# yum install MySQL-python.x86_64 -y
#调用python接口模块，为方便使用设置别名为mysql：
In [1]: import MySQLdb as mysql
#连接数据库：
In [3]: mysql.connect(user="root",passwd="redhat",host='127.0.0.1',charset='utf8')   #用户名root，密码redhat，数据库地址127.0.0.1，编码格式utf8
Out[3]: <_mysql.connection open to '127.0.0.1' at 1f1a000>

#为方便使用，给一个变量：
In [5]: conn = mysql.connect(user="root",passwd="redhat",host='127.0.0.1',charset='utf8')
#建立游标：
In [6]: cur = conn.cursor()
#选择数据库：
In [7]: conn.select_db('users')
*也可以在连接数据库的时候加上db='users'参数就可以省略这一步：conn = mysql.connect(user="root",passwd="redhat",db='users',host='127.0.0.1',charset='utf8')
#对数据库发送命令：
In [10]: cur.execute('insert into userinfo values("user1","123");')
Out[10]: 1L
#使上述命令生效：
In [11]: conn.commit()
#关闭游标(先关闭游标再关闭数据库连接)：
In [12]: cur.close()
#关闭数据库连接：
In [13]: conn.close()

练习1：

随机生成5个人名和对应的密码;人名由三个汉字或者2个汉字组成,姓 = ['王','张','苏','赵','李','朱','杨','白','刘','上官','叶','皇甫','慕容']名 = ['彬','丸','盼','空','龙','欢','文','青','琴','维','真','安','可','慈']密码统一6位, 由字母和字符组成;存储上述用户信息到数据库中, 保存在数据库users中的userinfo表中;

首先在数据库中手动建立users库和userinfo表：

MariaDB [(none)]> create database users;

MariaDB [(none)]> use users

MariaDB [users]> create table userinfo(Name varchar(50),PassWord varchar(50));

# coding:utf-8
import string,random
import MySQLdb as mysql
surname=['王','张','苏','赵','李','朱','杨','白','刘','上官','叶','皇甫','慕容']
name=['彬','丸','盼','空','龙','欢','文','青','琴','维','真','安','可','慈']

def Full_Name():  #生成名字
    xing=random.sample(surname,1)
    ming1=random.sample(name,1)
    ming2=["".join(random.sample(name,2))]
    ming=random.sample(ming1+ming2,1)
    xingming=xing+ming
    return "".join(xingming)

def PassWord(x):  #生成密码
    all_str=string.letters+string.digits  #string.letters生成所有字母(包括大写和小写)，string.digits生成数字
    password=random.sample(all_str,x)  #random.sample(str,sep)表示从str字符串中取出sep个字符
    return "".join(password)  #"".join(sep)表示以""为分隔符,将序列seq中的所有元素合并为一个新的字符串


def MariaDB(x):  #写入数据库
    user=Full_Name()
    passwd=PassWord(6)
    conn=mysql.connect(user='root',passwd='redhat',db='users',host='127.0.0.1',charset='utf8')
    cur=conn.cursor()
    sql_cmd="insert into userinfo values('%s','%s');" %(user,passwd)  #将SQL命令写在execute()方法外面，可以将变量传进去
    print "insert[%d]> %s,%s" %(x,user,passwd)
    cur.execute(sql_cmd)
    conn.commit()
    cur.close()
    conn.close()

i=1
while i <= 5:
    MariaDB(i)
    i+=1

执行结果：

查看数据库：

将一个列表中的数据传入数据库

In [2]: import MySQLdb as mysql

In [3]: conn = mysql.connect(user="root",passwd="redhat",db='users',host='127.0.0.1',charset='utf8')

In [4]: cur = conn.cursor()

In [5]: li = [("user1","123"),("user2","456"),("user3","789")]

In [6]: sql_cmd = 'insert into userinfo values("%s","%s");'

方法一：使用for循环

In [8]: for i in li:
   ...:     cur.execute(sql_cmd,i)
   ...:     
In [12]: conn.commit()方法二：使用executemany()内置方法
In [14]: cur.executemany(sql_cmd,li)
Out[14]: 3L

In [15]: conn.commit()

In [16]: cur.close()

In [17]: conn.close()

执行结果：由于刚才使用两种方法执行了列表导入数据库，因此执行结果也有两组数据

fetch内置方法(fetchone(),fetchmany(num),fethall())

#它们返回的结果都是元组

In [1]: import MySQLdb as mysql
In [2]: conn = mysql.connect(user="root",passwd="redhat",db='users',host='127.0.0.1',charset='utf8')
In [3]: cur = conn.cursor()
In [4]: cur.execute('select * from userinfo;')
Out[4]: 6L

fetchone()方法

每使用一次打印出一行数据(根据数据库表中的内容逐行显示)，如果显示完表中的所有数据之后就不会再打印数据了

In [5]: cur.fetchone()
Out[5]: (u"'user1'", u"'123'")

　　　　　　　　...

In [10]: cur.fetchone()
Out[10]: (u"'user3'", u"'789'")

In [11]: cur.fetchone()

#userinfo表中一共有6行数据，可以看到第7次使用fetchone已经没有数据输出了。其实还可以使用scroll(0,'absolute')将游标移至0位就可以从头开始打印了：

In [12]: cur.scroll(0,'absolute')

In [13]: cur.fetchone()
Out[13]: (u"'user1'", u"'123'")

fetchmany(num)方法

打印表中num行数据，例如num为2就是每次打印2行，如果显示完表中的所有数据之后就不会再打印数据了，同样可以使用scroll(0,'absolute')方法

In [18]: cur.execute('select * from userinfo;')
Out[18]: 6L

In [19]: cur.fetchmany(2)
Out[19]: ((u"'user1'", u"'123'"), (u"'user2'", u"'456'"))

fetchall()方法

显示表中所有数据，同样可以使用scroll(0,'absolute')方法

In [25]: cur.execute('select * from userinfo;')
Out[25]: 6L

In [26]: cur.fetchall()
Out[26]: 
((u"'user1'", u"'123'"),
 (u"'user2'", u"'456'"),
 (u"'user3'", u"'789'"),
 (u"'user1'", u"'123'"),
 (u"'user2'", u"'456'"),
 (u"'user3'", u"'789'"))

In [27]: cur.fetchall()
Out[27]: ()

In [28]: cur.scroll(0,'absolute')

In [29]: cur.fetchall()
Out[29]: 
((u"'user1'", u"'123'"),
 (u"'user2'", u"'456'"),
 (u"'user3'", u"'789'"),
 (u"'user1'", u"'123'"),
 (u"'user2'", u"'456'"),
 (u"'user3'", u"'789'"))

练习2：

判断某个数据库表是否存在

# coding:utf-8
# describe:待优化：将get_all_table改为生成器减少查询压力
import MySQLdb as mysql

def MariaDB(x):
    conn=mysql.connect(user='root',passwd='redhat',host='127.0.0.1',charset='utf8')
    cur=conn.cursor()
    cur.execute(x)
    return cur.fetchall()
    conn.commit()
    cur.close()
    conn.close()

def get_all_table():
    li_db=[]
    li_table=[]
    for i in MariaDB("show databases;"):
        li_db.append(i[0])
        for n in li_db:
            sql_cmd = "show tables from %s" %(n)
            for m in MariaDB(sql_cmd):
                li_table.append(m[0])
    return li_table

def check_table():
    value=raw_input("Input a table name: ")
    if value in get_all_table():
        print "%s exist!" %(value)
    else:
        print "%s not exist" %(value)

check_table()

执行结果：

练习3：

封装MySQLdb封装一个数据库类为MyMySQL;实例化对象时, 自动连接数据库, 自动创建游标;删除实例化对象时, 自动关闭游标, 断开数据库连接;编写select_table(self, table_name, num)方法, 显示该表里面的n条数据;编写create_table(self, table_name, **attr )方法, 实现创建表, 创建前判断表是否存在, **attr是表的属性名;

# coding:utf-8
# describe:未完成
import MySQLdb as mysql

class MyMySQL(object):
    def __init__(self,table_name,num,**attr):
        self.table = table_name
        self.num = num


    def select_table(self):
        conn=mysql.connect(user='root',passwd='redhat',db='users',host='127.0.0.1',charset='utf8')
        cur=conn.cursor()
        sql_cmd="select * from %s limit %s;" %(self.table,self.num)
        cur.execute(sql_cmd)
        return cur.fetchall()
        conn.commit()
        cur.close()
        conn.close()

    def create_table(self,table_name,**attr):
        pass

p1=MyMySQL('userinfo',2)
print p1.select_table()