pythonDB api的学习

有时候需要操作数据库，为了能使用统一的接口访问，我们采用Python DB API,地址为

https://www.python.org/dev/peps/pep-0249/

全文参考---“疯狂的蚂蚁crazyant”

我使用的是mysql+pymysql+pycharm连接数据库，windows本地要安装mysql数据库

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3307,
    user = 'root',
    password = 'root',
    db = 'songqin',
    charset = 'utf8',
    
)
#获取一个游标
cursor = conn.cursor()
print(conn)  #<pymysql.connections.Connection object at 0x000001F42FD05898>
print(cursor) #<pymysql.cursors.Cursor object at 0x000001F4319B59E8>

cursor.close()
conn.close()

　　执行查询语句

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3307,
    user = 'root',
    password = 'root',
    db = 'songqin',
    charset = 'utf8',
  
)
#获取一个游标
cursor = conn.cursor()
sql = 'select * from user'
cursor.execute(sql)

print(cursor.rowcount)

rs = cursor.fetchone()
print(rs)  #(1, 'name1')

rs = cursor.fetchmany(3)
print(rs)  #((2, 'name2'), (3, 'name3'), (4, 'name4'))

rs = cursor.fetchall()
print(rs) #((5, 'name5'), (6, 'name6'), (7, 'name7'), (8, 'name8'), (9, 'name9'))

cursor.close()
conn.close()

执行查询

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host = '127.0.0.1',
 5     port = 3307,
 6     user = 'root',
 7     password = 'root',
 8     db = 'songqin',
 9     charset = 'utf8',
10     #cursorclass = pymysql.cursors.DictCursor
11 )
12 #获取一个游标
13 cursor = conn.cursor()
14 sql = 'select * from user'
15 cursor.execute(sql)
16 
17 rs = cursor.fetchall()
18 print(rs)
19 for i in rs:
20     #print('userid=%s,username=%s' % (i[0],i[1]))
21     print('userid=%s,username=%s' % i)
22 
23 cursor.close()
24 conn.close()
View Code

执行增删改

 1 import pymysql
 2 
 3 conn = pymysql.connect(
 4     host = '127.0.0.1',
 5     port = 3307,
 6     user = 'root',
 7     password = 'root',
 8     db = 'songqin',
 9     charset = 'utf8',
10     #cursorclass = pymysql.cursors.DictCursor
11 )
12 #获取一个游标
13 cursor = conn.cursor()
14 sql_insert = "insert into user(userid,username) values(10,'name10')"
15 sql_update = "update user set username='name91' where userid=9"
16 sql_delete = "delete from user where userid<3"
17 
18 cursor.execute(sql_insert)
19 print(cursor.rowcount)
20 
21 cursor.execute(sql_update)
22 print(cursor.rowcount)
23 
24 cursor.execute(sql_delete)
25 print(cursor.rowcount)#受影响的行数
26 #发现数据库并没有改变，而让其改变的话，只能提交commit
27 
28 conn.commit()
29 
30 cursor.close()
31 conn.close()
View Code

异常回滚：

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3307,
    user = 'root',
    password = 'root',
    db = 'songqin',
    charset = 'utf8',
    #cursorclass = pymysql.cursors.DictCursor
)
#获取一个游标
cursor = conn.cursor()
sql_insert = "insert into user(userid,username) values(10,'name10')"
sql_update = "update user set username='name91' where userid=9"
sql_delete = "delete from user where userd<3" #这里故意写错

try:
    cursor.execute(sql_insert)
    print(cursor.rowcount)

    cursor.execute(sql_update)
    print(cursor.rowcount)

    cursor.execute(sql_delete)
    print(cursor.rowcount)#受影响的行数
    #发现数据库并没有改变，而让其改变的话，只能提交commit

    conn.commit()
except Exception as e:
    print(e)
    conn.rollback()  #数据回滚到之前的状态

cursor.close()
conn.close()

 

posted @ 2017-10-10 15:16  我要成为女技术宅  阅读(375)  评论(0)  编辑  收藏  举报