详解python之数据库mysql操作

前言: 最近开始学django了，学了下web框架，顿时感觉又会了好多知识。happy~~ 这篇博客整理写下数据库基本操作，内容挺少。明天写SQLAlchemy。

import pymysql
#生成实例，连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
#插入数据
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Jack','man',25,1351234,"CN"))
reCount = cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)',('Mary','female',18,1341234,"USA"))

conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount) 

mysql> select* from students;+----+------+-----+-----+-------------+------+
| id | name | sex | age | tel         | nal  |
+----+------+-----+-----+-------------+------+
|  1 | zcl  | man |  22 | 15622341234 | NULL |
|  2 | alex | man |  30 | 15622341235 | NULL |
+----+------+-----+-----+-------------+------+
2 rows in set 

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
cur = conn.cursor()

reCount = cur.execute('select* from students')

res = cur.fetchone()       #获取一条数据
res2 = cur.fetchmany(3)   #获取3条数据
res3 = cur.fetchall()     #获取所有(元组格式)
print(res)
print(res2)
print(res3)
conn.commit()

cur.close()
conn.close() 

(1, 'zcl', 'man', 22, '15622341234', None)
((2, 'alex', 'man', 30, '15622341235', None), (5, 'Jack', 'man', 25, '1351234', 'CN'), (6, 'Mary', 'female', 18, '1341234', 'USA'))
() 

import pymysql
#连接数据库zcl
conn=pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur=conn.cursor()
#插入数据
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Jack', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s,%s,%s,%s,%s)', ('Jack2', 'man', 25, 1351234, "CN"))
reCount=cur.execute('insert into students(name, sex, age, tel, nal) values(%s, %s, %s, %s, %s)', ('Mary', 'female', 18, 1341234, "USA"))

conn.rollback()      #事务回滚
conn.commit()        #实例提交命令

cur.close()
conn.close()
print(reCount) 

mysql> select* from students;+----+------+--------+-----+-------------+------+
| id | name | sex    | age | tel         | nal  |
+----+------+--------+-----+-------------+------+
|  1 | zcl  | man    |  22 | 15622341234 | NULL |
|  2 | alex | man    |  30 | 15622341235 | NULL |
|  5 | Jack | man    |  25 | 1351234     | CN   |
|  6 | Mary | female |  18 | 1341234     | USA  |
+----+------+--------+-----+-------------+------+
4 rows in set 

mysql> select* from students;+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set 

import pymysql
#连接数据库zcl
conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='zcl')
#生成游标，当前实例所处状态
cur = conn.cursor()
li = [
    ("cjy","man",18,1562234,"USA"),
    ("cjy2","man",18,1562235,"USA"),
    ("cjy3","man",18,1562235,"USA"),
    ("cjy4","man",18,1562235,"USA"),
    ("cjy5","man",18,1562235,"USA"),
]

#插入数据
reCount = cur.executemany('insert into students(name,sex,age,tel,nal) values(%s,%s,%s,%s,%s)', li)

#conn.rollback()  #事务回滚
conn.commit()  #实例提交命令

cur.close()
conn.close()
print(reCount) 

pycharm下 输出: 5

mysql终端显示:

mysql> select* from students;   　  #插入数据前+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
+----+-------+--------+-----+-------------+------+
7 rows in set


mysql> mysql> select* from students;　　　#插入数据后+----+-------+--------+-----+-------------+------+
| id | name  | sex    | age | tel         | nal  |
+----+-------+--------+-----+-------------+------+
|  1 | zcl   | man    |  22 | 15622341234 | NULL |
|  2 | alex  | man    |  30 | 15622341235 | NULL |
|  5 | Jack  | man    |  25 | 1351234     | CN   |
|  6 | Mary  | female |  18 | 1341234     | USA  |
| 10 | Jack  | man    |  25 | 1351234     | CN   |
| 11 | Jack2 | man    |  25 | 1351234     | CN   |
| 12 | Mary  | female |  18 | 1341234     | USA  |
| 13 | cjy   | man    |  18 | 1562234     | USA  |
| 14 | cjy2  | man    |  18 | 1562235     | USA  |
| 15 | cjy3  | man    |  18 | 1562235     | USA  |
| 16 | cjy4  | man    |  18 | 1562235     | USA  |
| 17 | cjy5  | man    |  18 | 1562235     | USA  |
+----+-------+--------+-----+-------------+------+
12 rows in set 

学完的东西要及时总结，有些东西忘记了阿~_~

更多详解python之数据库mysql操作相关文章请关注PHP中文网！

查看更多关于详解python之数据库mysql操作的详细内容...