pymysql
1.安装pymysql库： pip install pymysql / pip3 install pymysql -i https://pypi.douban.com/simple
Pycharm IDE安装：
1.
2.
3.
使用此模块需要在电脑上已经安装并配置好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()
使用连接模仿用户登录：
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('登录失败')
基本增删改查操作：
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()
