Python database operations, python Database

Python database operations, python Database
Database Operations

Install the Mysql module using Python

12345

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

Basic SQL usage

1. Database Operations

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

2. Data Table operations

12345678910

show tables; create table students    (        id intnot 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 );Create a table wb_blog

3. Data Operations

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. Others

123	Primary KeyForeign keyLeft and right connections

Python MySQL API

I. insert data

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 = '2017. 0.0.1 ', user = 'root', passwd = '000000', db = 'mydb') cur = conn. cursor () li = [('Alex ', 'USA'), ('SB ', 'USA'),] reCount = cur.exe cute.pdf ('insert into UserInfo (Name, address) values (% s, % s) ', li) conn. commit () cur. close () conn. close () print reCountMySQLdb

Note: cur. lastrowid

Ii. delete data

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

3. modify data

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

Iv. Data Query

# ############################## 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]