Python操作MySQL
Python操作Mysql 模块的安装
yum install MySQL
-
python
Python操作MySQL
Python操作MySQL SQL基本使用 1、数据库操作 show databases; use [databasename]; create database [name]; 2、数据表操作 show tables; create table students ( id int not null auto_increment primary key, name char(8) not null, sex char(4) not null, age tinyint unsigned not null, tel char(13) null default "-" ); ### CREATE TABLE `wb_blog` ( `id` smallint(8) unsigned NOT NULL, `catid` smallint(5) unsigned NOT NULL DEFAULT '0', `title` varchar(80) NOT NULL DEFAULT '', `content` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `catename` (`catid`) ) ; 3、数据操作(增删改查) insert into students(name,sex,age,tel) values('fly','man',18,'151515151') delete from students where id =2; update students set name = 'flying' where id =1; select * from students;
Python MySQL API
执行sql
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') # 创建游标 cursor = conn.cursor() # 执行SQL,并返回收影响行数 effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL,并返回受影响行数 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,)) # 执行SQL,并返回受影响行数 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) # 提交,不然无法保存新建或者修改的数据 conn.commit() # 关闭游标 cursor.close() # 关闭连接 conn.close()
1、插入数据
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') cur = conn.cursor() reCount = cur.execute('insert into UserInfo(Name,Address) values(%s,%s)',('flying','usa')) # reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'flyoss'}) conn.commit() cur.close() conn.close() print reCount
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') cur = conn.cursor() info =[ ('fly','usa'), ('flying','usa'), ] reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',info) conn.commit() cur.close() conn.close() print reCount 批量插入数据
2、删除数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') cur = conn.cursor() reCount = cur.execute('delete from UserInfo') conn.commit() cur.close() conn.close() print reCount
3、修改数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') cur = conn.cursor() reCount = cur.execute('update UserInfo set Name = %s',('alin',)) conn.commit() cur.close() conn.close() print reCount
4.查数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
# ############################## fetchone/fetchmany(num) ############################## import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') cur = conn.cursor() reCount = cur.execute('select * from UserInfo') print cur.fetchone() print cur.fetchone() cur.scroll(-1,mode='relative') print cur.fetchone() print cur.fetchone() cur.scroll(0,mode='absolute') print cur.fetchone() print cur.fetchone() cur.close() conn.close() print reCount # ############################## fetchall ############################## import MySQLdb conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb') #cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) cur = conn.cursor() reCount = cur.execute('select Name,Address from UserInfo') nRet = cur.fetchall() cur.close() conn.close() print reCount print nRet for i in nRet: print i[0],i[1]