python操作mysql

# rpm -qa |grep MySQL-python 查询是否有mysqldb库 MySQL-python-1.2.3-0.3.c1.1.el6.x86_64

>>> import MySQLdb #导入mysqldb模块 >>> conn = MySQLdb.connect(user='root',passwd='',host='127.0.0.1') #设置连接参数

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

>>> conn.select_db('test') #选中数据库test进行连接

>>> cur.execute("insert into t1(id,name,age) value(3,'cc',30)") #发送sql指令，增加一条记录 1L  #显示增加一行记录

mysql> select * from t1; +------+------+------+ | id   | name | age  | +------+------+------+ |    1 | aa   | 10   | |    2 | bb   | 20   | |    3 | cc   | 30   |

>>> sqli = "insert into t1(id,name,age) value(%s,%s,%s)" #定义插入字符串

>>> cur.execute(sqli,(7,'ll',70)) #执行插入指令的，调插入字符串 1L

>>> cur.executemany(sqli,[(8,'rr',80),(9,'yy',90)]) #插入多行使用many 2L

mysql> select * from t1; +------+------+------+ | id   | name | age  | +------+------+------+ |    1 | aa   | 10   | |    2 | bb   | 20   | |    3 | cc   | 30   | |    4 | dd   | 40   | |    5 | gg   | 50   | |    6 | ff   | 60   | |    7 | ll   | 70   | |    8 | rr   | 80   | |    9 | yy   | 90   | +------+------+------+ 9 rows in set (0.00 sec)

>>> cur.execute('delete from t1 where id = 4') #删除操作 1L

mysql> select * from t1; +------+------+------+ | id   | name | age  | +------+------+------+ |    1 | aa   | 10   | |    2 | bb   | 20   | |    3 | cc   | 30   | |    5 | gg   | 50   | |    6 | ff   | 60   | |    7 | ll   | 70   | |    8 | rr   | 80   | |    9 | yy   | 90   | +------+------+------+ 8 rows in set (0.00 sec)

>>> cur.execute("update t1 set name = 'uu' where id = 7") #修改操作 1L

mysql> select * from t1; +------+------+------+ | id   | name | age  | +------+------+------+ |    1 | aa   | 10   | |    2 | bb   | 20   | |    3 | cc   | 30   | |    5 | gg   | 50   | |    6 | ff   | 60   | |    7 | uu   | 70   | |    8 | rr   | 80   | |    9 | yy   | 90   | +------+------+------+ 8 rows in set (0.00 sec)

>>> cur.execute('select * from t1') #查询，不能反映出来 8L

>>> cur.fetchone() #显示一行 (1L, 'aa', '10')

>>> cur.fetchmany(7) #显示七行 ((2L, 'bb', '20'), (3L, 'cc', '30'), (5L, 'gg', '50'), (6L, 'ff', '60'), (7L, 'uu', '70'), (8L, 'rr', '80'), (9L, 'yy', '90'))

>>> cur.fetchmany(7) #不可以重复取数据 ()

>>> cur.scroll(0,'absolute') #光标移动到开头位置

>>> cur.fetchmany(7) #可以继续去数据 ((1L, 'aa', '10'), (2L, 'bb', '20'), (3L, 'cc', '30'), (5L, 'gg', '50'), (6L, 'ff', '60'), (7L, 'uu', '70'), (8L, 'rr', '80'))

>>> cur.fetchmany(cur.execute("select * from t1")) #查询表中所有数据条目 ((1L, 'aa', '10'), (2L, 'bb', '20'), (3L, 'cc', '30'), (5L, 'gg', '50'), (6L, 'ff', '60'), (7L, 'uu', '70'), (8L, 'rr', '80'), (9L, 'yy', '90')) >>> cur.close() #关闭游标 >>> conn.close() #关闭数据库