Python operation MySQL

Python Operation Mysql Module installation

linux：

     yum install MySQL - python
Installing PYTHON-MYSQLDB

Tar zxf mysql-python-1.2.3c1.tar.gzcd mysql-python-1.2.3c1python setup.py Install  

SQL Common operations:

1. Database operation

Show databases;use [databasename];create database  [name];

2. Data table operation

Show tables; CREATE TABLE students    (        ID int not  NULL Auto_increment primary key,        name char (8) is not NULL,        sex char (4 ) not NULL, an age of        tinyint unsigned NOT NULL,        Tel char (+) NULL default "-"    );

3. Data manipulation

INSERT into students (Name,sex,age,tel) VALUES (' Alex ', ' Mans ', ' 151515151 ') delete from students where id = 2; Update students set name = ' SB ' WHERE id = 1; SELECT * FROM Students

############## Query Select######################

Fetchall

Import mysqldb      #导入连接数据的模块conn = MySQLdb.connect (host= ' 10.10.50.30 ', user= ' root ', passwd= ' boyojoy.com ', db= ' YANGMV ') #cur = conn.cursor (Cursorclass = MySQLdb.cursors.DictCursor)    #以字典的格式返回结果, default is tuple cur = conn.cursor () recount = Cur.execute (' select * from user ') #execute得到执行命令结果的行数data = Cur.fetchall ()                       #fetchall () gets all data in the database Cur.close () Conn.close () Print recountprint data

　　

Fetchone,fetchmany

Import Mysqldbconn = MySQLdb.connect (host= ' 127.0.0.1 ', user= ' root ', passwd= ' 1234 ', db= ' mydb ') cur = conn.cursor () recount = Cur.execute (' select * from user ') print cur.fetchone ()    #执行一次, returns the first Data print cur.fetchone ()    #每次二次, Returns the second data print Cur.fetchmany (4)  #获取前4条数据 # pointer position cur.scroll (0,mode= ' absolute ')   #绝对位置, 0 coordinates cur.scroll ( -1,mode= ' Relative ')  #相对位置, current position left move 1print Cur.fetchone () cur.close () Conn.close ()

　　

############## inserting Insert#######################

Import Mysqldbconn = MySQLdb.connect (host= ' 10.10.50.30 ', user= ' root ', passwd= ' boyojoy.com ', db= ' yangmv ') cur = Conn.cursor () sql = "INSERT into user (name,address) value (%s,%s)"    #sql语句params = (' Jay ', ' Taiwan ') recount = Cur.execute (sql,params)      #插入一条数据conn. Commit ()                          #提交至mysql生效 #cur.lastrowid                         #获取插入数据的自增IDcur. Close () Conn.close () print recount   #查看成功执行了几条命令

　　

BULK INSERT Data

Import Mysqldbconn = MySQLdb.connect (host= ' 10.10.50.30 ', user= ' root ', passwd= ' boyojoy.com ', db= ' yangmv ') cur = Conn.cursor () Li =[('     Alex ', ' USA '), (' SB ', ' Japan '),]recount = Cur.executemany (' INSERT into user (name,address) VALUES (%s,%s) ', Li ' Conn.commit () cur.close () conn.close () print recount

　　

############## Remove Delete#######################

Import Mysqldbconn = MySQLdb.connect (host= ' 10.10.50.30 ', user= ' root ', passwd= ' boyojoy.com ', db= ' yangmv ') cur = Conn.cursor () sql = "Delete from user where id =%s"    #sql语句params = (1,)                           #删除id为1的数据reCount = Cur.execute (sql,params )      #删除一条数据conn. Commit () Cur.close () conn.close () print recount

　　

############## Change Update#######################

Import Mysqldbconn = MySQLdb.connect (host= ' 10.10.50.30 ', user= ' root ', passwd= ' boyojoy.com ', db= ' yangmv ') cur = Conn.cursor () sql = "Update user set name =%s where name = ' WJ '" #把wj更改为yangmvparams = (' YANGMV ',) recount = cur.execute (sq L,params)      #修改数据conn. Commit () Cur.close () conn.close () print recount

　　

Python operation MySQL