第二十四天学习:mysql(一)
一、安装客户端
python DB-API使用流程
1.引入API模块
2.获取与数据库的连接
3.执行SQL语句和存储过程
4.关闭数据库连接
安装包路径:https://pypi.python.org/pypi/MySQL-python/1.2.5
windows:
windows 下载exe结尾的包,然后安装
安装完后在cmd中执行:
C:\Users\test>python Python 2.7.10 (default, May 23 2015, 09:40:32) [MSC v.1500 32 bit (Intel)] on w n32 Type "help", "copyright", "credits" or "license" for more information. >>> import MySQLdb >>>
如果出现ImportError: No module named MySQLdb
则是没有把安装包路径添加到环境变量中去。
linux
选择zip结尾的包
yum install -y python-devel mysql-devel
unzip MySQL-python-1.2.5.zip
cd MySQL-python-1.2.5
python setup.py build
echo $?
python setup.py install
# python Python 2.6.6 (r266:84292, Aug 18 2016, 14:53:48) [GCC 4.4.7 20120313 (Red Hat 4.4.7-17)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> import MySQLdb >>>
本地安装mysql-server版本:mysql6.5
进行授权
mysql
mysql>create database python;
mysql> create user 'test'@'localhost' identified by '123456';
mysql> grant all privileges on *.* to 'test'@'localhost' identified by '123456';
mysql> flush privileges;
注:
create创建用户
命令:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
授权:
命令:GRANT privileges ON databasename.tablename TO 'username'@'host' by 'password';
说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示, 如*.*. ,密码,要赋给远程访问权限的用户对应使用的密码
查询用户:
SELECT User, Host, Password FROM mysql.user;
二、数据库连接
常用参数:
host 主机名
user 登录名
passwd 登录密码
db 数据库名
port tcp端口,默认3306
charset 字符集
import MySQLdb #conn = MySQLdb.connect(host='127.0.0.1', user='test', passwd='123456', db='python', charset='utf8', prot=3306) # 构造函数 def connect_mysql(): db_config = { 'host' : '127.0.0.1', 'port' : 3306, 'user' : 'test', 'passwd' : '123456', 'db' : 'python', 'charset' : 'utf-8' } try: conn = MySQLdb.connect(**db_config) except Exception as e: raise e return conn
三、mysql事务
事务: 主要用于处理操作量大,复杂度高的数据。
必须满足的4个条件(ACID):
Atomicity 原子性,一组事务要么成功,要么撤回
Consistency 稳定性,有非法数据(外键约束之类),事务撤回
Isolation 隔离性,事务独立运行,一个事务处理后的结果,影响了其他事务,那么其他事务会撤回,事务的100%隔离,需要牺牲速度
Durability 可靠性,软硬件奔溃外,inndb数据表驱动会利用日志文件重构修改,可靠性与高速度性不可兼得
mysql> show variables like "auto%"; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | +--------------------------+-------+ 4 rows in set (0.01 sec)
autocommit 自动提交
mysql事务方法:
commit() 提交当前事务
rollback() 取消当前事务
四、mysql操作
# vim mysql_conn.py #!/usr/bin/python #-*- coding:utf-8 -*- import MySQLdb def connect_mysql(): db_config = { 'host' : 'localhost', 'port' : 3306, 'user' : 'test', 'passwd' : '123456', 'db' : 'python', # 'charset' : 'utf-8' } try: conn = MySQLdb.connect(**db_config) except Exception as e: raise e return conn if __name__ == '__main__': sql = 'create table test(id int not null); insert into test(id) values(100);' conn = connect_mysql() cus = conn.cursor() #创建游标 try: cus.execute(sql) cus.close() conn.commit() except Exception as e: raise e finally: cus.close() 验证结果: # mysql -utest -p123456 mysql> use python; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_python | +------------------+ | test | | test01 | +------------------+ 2 rows in set (0.01 sec) mysql> select * from test; +-----+ | id | +-----+ | 100 | +-----+ 1 row in set (0.00 sec) mysql>