python之資料庫操作，python資料庫

python之資料庫操作，python資料庫
資料庫操作

Python 操作 Mysql 模組的安裝

12345

linux：    yum install MySQL-python window：    http://files.cnblogs.com/files/wupeiqi/py-mysql-win.zip

SQL基本使用

1、資料庫操作

123	show databases; use [databasename];create database  [name];

2、資料表操作

12345678910

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 "-"    );

1 CREATE TABLE `wb_blog` ( 2 `id` smallint(8) unsigned NOT NULL, 3 `catid` smallint(5) unsigned NOT NULL DEFAULT '0', 4 `title` varchar(80) NOT NULL DEFAULT '', 5 `content` text NOT NULL, 6 PRIMARY KEY (`id`), 7 UNIQUE KEY `catename` (`catid`) 8 ) ; 建立表wb_blog

3、資料操作

1234567

insert into students(name,sex,age,tel) values('alex','man',18,'151515151') delete from students where id =2; update students set name = 'sb' where id =1; select * from students

4、其他

123

主鍵外鍵左右串連

Python MySQL API

一、插入資料

123456789101112131415

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)',('alex','usa'))# reCount = cur.execute('insert into UserInfo(Name,Address) values(%(id)s, %(name)s)',{'id':12345,'name':'wupeiqi'})  conn.commit()  cur.close()conn.close()  print reCount

import MySQLdbconn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='1234',db='mydb')cur = conn.cursor()li =[ ('alex','usa'), ('sb','usa'),]reCount = cur.executemany('insert into UserInfo(Name,Address) values(%s,%s)',li)conn.commit()cur.close()conn.close()print reCountMySQLdb

注意：cur.lastrowid

二、刪除資料

1234567891011121314

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

三、修改資料

12345678910111213

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

四、查資料

# ############################## 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 reCountprint nRetfor i in nRet:    print i[0],i[1]