python操作mysql

2023-01-31 03:01:45 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() #关闭数据库

相关文章