32-Python3 MySQL(mysql-connector)
2018-11-20 13:30 改改~_~ 阅读(550) 评论(0) 编辑 收藏 举报32-Python3 MySQL(mysql-connector)
''' 创建数据库连接 ''' import pymysql mydb = pymysql.connect( host = '127.0.0.1', user = 'root', passwd = 'root', db = 'jo_db1', port = 3306, charset = 'utf8' ) print('mydb:',mydb) mycursor = mydb.cursor() ''' 创建数据库 ''' # mycursor.execute('CREATE DATABASE runoob_db1') ##CREATE DATABASE必须为大写,否则报语法错误 ##输出所有数据库列表 mycursor.execute('SHOW DATABASES') for x in mycursor: print(x) ''' 创建数据库表 ''' import pymysql mydb = pymysql.connect( host = '127.0.0.1', user = 'root', passwd = 'root', database = 'runoob_db1' ) mycursor = mydb.cursor() ##创建数据库 mycursor.execute('CREATE TABLE site(name VARCHAR(255),url VARCHAR(255))') ##打印输出数据库中所有的表 mycursor.execute('SHOW TABLES') for x in mycursor: print(x) ##主键设置:给已创建的表添加主键 mycursor.execute('ALTER TABLE site ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY') ##主键设置:创建表时添加主键 import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1' ) mycursor = mydb.cursor() mycursor.execute('CREATE TABLE site2 (id INT AUTO_INCREMENT PRIMARY KEY ,name VARCHAR (255),site VARCHAR (255))') ''' 插入数据 ''' import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1' ) mycursor = mydb.cursor() mycursor.execute('CREATE TABLE site2 (id INT AUTO_INCREMENT PRIMARY KEY ,name VARCHAR (255),site VARCHAR (255))') ##插入一条数据 val =('RUNOOB','https://www.runoob.com') mycursor.execute(sql,val) mydb.commit() print(mycursor.rowcount,'记录插入成功。') ##批量插入 val = [ ('TAOBAO','https://www.taobao.com'), ('Google','https://google.com'), ('BaiDu','https://baidu.com'), ] mycursor.executemany(sql,val) mydb.commit() print(mycursor.rowcount,'批量记录插入成功。') ##获取插入数的id val = ('Zhihu','https://www.zhihu.com') mycursor.execute(sql,val) mydb.commit() print('1条记录已插入,ID为:',mycursor.lastrowid) ''' 查询数据 ''' import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() mycursor.execute('SELECT * FROM site2') ##获取所有数据 # myresult = mycursor.fetchall() ##如果只想获取一条数据的话 myresult = mycursor.fetchone() for x in myresult: print(x) ##where条件语句(完整版)------------------------------------------------------ import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() sql = "select * from site2 where name = 'runoob'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) ##模糊查询完整版 import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() sql = "select * from site2 where site like '%oo%'" #sql2 = "select * from site2" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) ##防止sql注入的 pass ##排序(完整版) import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() #sql = "select * from site2 order by name " #默认的正排序 sql = "select * from site2 order by name desc" #倒排序 sql = "select * from " mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) ##Limit import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() sql = "select * from site2" #sql = "select * from site2 order by name " #默认的正排序 #sql = "select * from site2 order by name desc" #倒排序 #sql = "select * from site2 limit 3" # 查询前3条记录 #sql = "select * from site2 limit 2 offset 1" #从第1条数据开始向上取3条记录 mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) ''' 删除记录 ''' import pymysql mydb = pymysql.connect(host = '127.0.0.1',user = 'root',passwd = 'root',database = 'runoob_db1') mycursor = mydb.cursor() #插入数据 sql= "insert into site2 (name,site) values (%s,%s)" val = ('Heheda','https://wwww.heheda.com') mycursor.execute(sql,val) mydb.commit() #查询所有结果 sql = "select * from site2" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) #删除数据 sql = "delete from site2 where name = 'Heheda'" mycursor.execute(sql) mydb.commit() ''' 更新表数据 ''' ''' 删除表 '''