pymysql

1.安装pymysql库： pip install pymysql / pip3 install pymysql -i https://pypi.douban.com/simple

Pycharm IDE安装：

使用此模块需要在电脑上已经安装并配置好mysql.

以下实例链接Mysql的TESTDB数据库：

 1 #!/usr/bin/python3
 2 
 3 import pymysql
 4 
 5 # 打开数据库连接
 6 db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 7 
 8 # 使用 cursor() 方法创建一个游标对象 cursor
 9 cursor = db.cursor()
10 
11 # 使用 execute()  方法执行 SQL 查询 
12 cursor.execute("SELECT VERSION()")
13 
14 # 使用 fetchone() 方法获取单条数据.
15 data = cursor.fetchone()
16 
17 print ("Database version : %s " % data)
18 
19 # 关闭数据库连接
20 db.close()

 1 # 导入MySQL驱动:
 2 >>> import mysql.connector
 3 # 注意把password设为你的root口令:
 4 >>> conn = mysql.connector.connect(user='root', password='password', database='test')
 5 >>> cursor = conn.cursor()
 6 # 创建user表:
 7 >>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
 8 # 插入一行记录，注意MySQL的占位符是%s:
 9 >>> cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])
10 >>> cursor.rowcount
11 1
12 # 提交事务:
13 >>> conn.commit()
14 >>> cursor.close()
15 # 运行查询:
16 >>> cursor = conn.cursor()
17 >>> cursor.execute('select * from user where id = %s', ('1',))
18 >>> values = cursor.fetchall()
19 >>> values
20 [('1', 'Michael')]
21 # 关闭Cursor和Connection:
22 >>> cursor.close()
23 True
24 >>> conn.close()

View Code

使用连接模仿用户登录：

 1 import pymysql
 2 
 3 user = input("username:")
 4 pwd = input("password:")
 5 
 6 conn = pymysql.connect(host="localhost",user='root',password='123',database="pysql")
 7 cursor = conn.cursor()
 8 sql = "select * from test where username=%s and password=%s"
 9 #sql = "select * from test where username=%(u)s and password=%(p)s"
10 cursor.execute(sql,[user,pwd])
11 # cursor.execute(sql,{'u':user,'p':pwd})
12 result = cursor.fetchone()
13 cursor.close()
14 conn.close()
15 if result:
16     print('登录成功')
17 else:
18     print('登录失败')

View Code

基本增删改查操作：

 1 import pymysql
 2 
 3 # 增加，删，该
 4 conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
 5 cursor = conn.cursor()
 6 sql = "insert into userinfo(username,password) values('root','123123')"
 7 # 受影响的行数
 8 r = cursor.execute(sql)
 9 #  ******
10 conn.commit()  #在执行增删改操作时需要提交事物，查则不需要。
11 cursor.close()
12 conn.close()
13 
14 conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
15 cursor = conn.cursor()
16 sql = "insert into userinfo(username,password) values(%s,%s)"
17 cursor.execute(sql,(user,pwd,))
18 
19 sql = "insert into userinfo(username,password) values(%s,%s)"
20 # 受影响的行数
21 r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')])  #添加多行内容用executemany
22 #  ******
23 conn.commit()
24 cursor.close()
25 conn.close()
26 
27 
28 
29 
30 # 查
31 conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
32 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  #查询时显示字典
33 sql = "select * from userinfo"
34 cursor.execute(sql)
35 
36 cursor.scroll(1,mode='relative')  # 相对当前位置移动
37 cursor.scroll(2,mode='absolute') # 相对绝对位置移动
38 result = cursor.fetchone()
39 print(result)
40 result = cursor.fetchone()    #类似文件操作的seek会一直往后移动。
41 print(result)
42 result = cursor.fetchone()
43 print(result)
44 result = cursor.fetchall()
45 print(result)
46 
47 
48 result = cursor.fetchmany(4)  #查看多条匹配数据
49 print(result)
50 cursor.close()
51 conn.close()
52 
53 
54 
55 
56 # 新插入数据的自增ID: cursor.lastrowid
57 import pymysql
58 
59 conn = pymysql.connect(host="localhost",user='root',password='',database="db666")
60 cursor = conn.cursor()
61 sql = "insert into userinfo(username,password) values('asdfasdf','123123')"
62 cursor.execute(sql)
63 conn.commit()
64 print(cursor.lastrowid)    #查看插入的最后数据的自增ID
65 cursor.close()
66 conn.close()

View Code