mysql-python模块
安装模块:
yum install mariadb-server -y
yum search python | grep mysql -i #-i忽略大小写
yum install MySQL-python.x86_64
查看数据库编码格式:
show variables like ‘character%’;
临时修改数据库编码格式
set character_set_server=’utf8’;
set character_set_database =’utf8’
永久更改数据库编码格式:
vim /etcmy.cnf.d/client.cnf
[client]
8 default-character-set=utf8
vim /etc/my.cnf.d/server.cnf
[mysqld]
13 character-set-server=utf8
连接数据库需要的参数
host: 数据库地址
user: 登陆数据库用户名
passwd:登陆数据库密码
db:登陆数据库后操作的数据库名
port:数据库监听端口,默认为3306
charset:数据库编码
commit():提交数据
cursor():游标指针
import MySQLdb as mysql
conn = mysql.connect(host='localhost',user='root',passwd='redhat',charset='utf8') ##连接数据库
cur = conn.cursor() #创建游标
cur.execute('create database aq1501;') #执行sql语句
cur.execute('use aq1501;')
cur.execute('create table userinfo (username varchar(20),id varchar(10));')
cur.execute('insert into userinfo values("haha","03153001");') #单条插入
cur.execute('insert into userinfo values("lala","03153002");')
cur.execute('select * from userinfo;')
print cur.fetchall() #多条显示,以元组形式
((u’haha’, u’03153001’), (u’lala’, u’03153002’))
sqli = 'insert into userinfo values(%s,%s);'
cur.executemany(sqli,[('xiaoming','03153003'),('danny','03153004')])
cur.execute('select * from userinfo;')
((u’xiaoming’, u’03153003’), (u’danny’, u’03153004’)) #多条输入
print cur.fetchone() #单条显示
print cur.fetchone()
(u’xiaoming’, u’03153003’)
((u’danny’, u’03153004’),)
print cur.fetchone()
cur.scroll(0,'absolute') #移动游标到开头
print cur.fetchall()
print cur.fetchone()
cur.scroll(0,’absolute’)
print cur.fetchall()
conn.commit() #提交数据,操作才能生效
cur.close() #关闭游标
conn.close() #关闭数据库连接
为了防止未保存的失误,因此建议用try,except,finally语句,具体示例如下
import MySQLdb as mysql
try:
conn = mysql.connect(host='localhost',user='root',db='aq1501',passwd='redhat',charset='utf8')
cur = conn.cursor()
sqli = 'insert into userinfo values(%s,%s);'
cur.executemany(sqli,[('xiaoming','03153003'),('danny','03153004')])
cur.execute('select * from userinfo;')
print cur.fetchone()
cur.scroll(0,'absolute')
print cur.fetchall()
conn.commit()
except:
pass
finally:
cur.close()
conn.close()