day9mysql操作

#!/usr/bin/env python

#coding:utf8

import MySQLdb

pip install MySQL-python

先创建一个表

mysql> use xym;

Database changed

mysql> 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 "-"

    ->     );

Query OK, 0 rows affected (0.05 sec)

mysql> show tables;   #查看所有表

+---------------+

| Tables_in_xym |

+---------------+

| students      |

+---------------+

1 row in set (0.01 sec)

mysql> desc  students;查看表结构

+-------+---------------------+------+-----+---------+----------------+

| Field | Type                | Null | Key | Default | Extra          |

+-------+---------------------+------+-----+---------+----------------+

| id    | int(11)             | NO   | PRI | NULL    | auto_increment |

| name  | char(8)             | NO   |     | NULL    |                |

| sex   | char(4)             | NO   |     | NULL    |                |

| age   | tinyint(3) unsigned | NO   |     | NULL    |                |

| tel   | char(13)            | YES  |     | -       |                |

+-------+---------------------+------+-----+---------+----------------+

5 rows in set (0.01 sec)

conn = MySQLdb.connect(host='127.0.0.1',user='root',db='xym')#连接数据库

cur = conn.cursor()#创建游标

re_count = cur.execute("insert into students(Name, sex, age, tel) values(%s, %s, %s, %s)",("xym", "man", 20, 137))

#执行SQL

li =[

     ('xym','Man',18,137),

     ('abc','Man',18,137),

]

re_count = cur.executemany('insert into students(Name, sex, age, tel) values(%s,%s,%s,%s)',li)

批量执行SQL

conn.commit()#提交

cur.close()#关闭游标

conn.close()#关闭连接

print re_count