python19 操作mysql
connect 模块下载
https://dev.mysql.com/downloads/connector/python/
import mysql.connector con = mysql.connector.connect( host = "192.168.111.153", port = "3306", user = "quan", password = "2004", database = "mon" ) con.close()
import mysql.connector config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } con = mysql.connector.connect(**config)
import mysql.connector config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } con = mysql.connector.connect(**config) cursor = con.cursor() sql = "SELECT id,classname FROM class;" cursor.execute(sql) for one in cursor: print(type(one)) print(one[0],one[1]) sql2 = "SHOW TABLES" cursor.execute(sql2) for i in cursor: print(i) 结果； <class 'tuple'> 1 dada <class 'tuple'> 2 jave <class 'tuple'> 4 py ('ALT',) ('class',) ('class_type',) ('fa',) ('father',) ('joson',) ('nu',) ('numm',) ('nummm',) ('provices',) ('qqq',) ('son',) ('ss',) ('tp3',) ('tp4',) ('tp5',) ('tp6',) ('user',)
实现sql注入；
SQL Injection：就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
具体来说，它是利用现有应用程序，将（恶意）的SQL命令注入到后台数据库引擎执行的能力，它可以通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。
防止：
1.永远不要信任用户的输入，要对用户的输入进行校验，可以通过正则表达式，或限制长度，对单引号和双"-"进行转换等。 2.永远不要使用动态拼装SQL，可以使用参数化的SQL或者直接使用存储过程进行数据查询存取。 3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。 4.不要把机密信息明文存放，请加密或者hash掉密码和敏感的信息。 5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装，把异常信息存放在独立的表中。
import mysql.connector config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } con = mysql.connector.connect(**config) ID = "1 OR 1 = 1" CN = "1 OR 1 = 1" sql = "SELECT COUNT(*) FROM class WHERE id ="+ID+" AND classname = "+CN; cursor = con.cursor() cursor.execute(sql) print(cursor.fetchone()[0]) con.close() 结果： 3
编译成二进制还不能执行，需要传入参数
传入参数之后二进制再进行执行，提高效率，不需要再分析磁盘这些步骤
输入的所有参数都是认为是字符串处理，不在分析，
import mysql.connector config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } con = mysql.connector.connect(**config) ID = "1 OR 1 = 1" CN = "1 OR 1 = 1" sql = "SELECT COUNT(*) FROM class WHERE id =%s AND classname = %s "; cursor = con.cursor() cursor.execute(sql,(ID,CN))#这里并没有给sql直接传入参数，而是让sql先编译成二进制再传入参数 print(cursor.fetchone()[0]) con.close() 结果： 0
con.commit()用于提交事务，connector不会为你自己提交
con.rollback（）回滚事务
con in dir()判断con变量是否创建
import mysql.connector try: con = mysql.connector.connect( host="192.168.111.153", port="3306", user="quan", password="2004", database="mon" ) con.start_transaction()#开启事务 cursor = con.cursor()#创建游标 sql = "INSERT INTO class(classname) VALUES (%s)" cursor.execute(sql,("QQQQ",)) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e) finally: if "con" in dir(): con.close()
结果：
import mysql.connector try: con = mysql.connector.connect( host="192.168.111.153", port="3306", user="quan", password="2004", database="mon" ) con.start_transaction()#开启事务 cursor = con.cursor()#创建游标 sql = "DELETE FROM class WHERE id = %s" cursor.execute(sql,(9,)) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e) finally: if "con" in dir(): con.close()
import mysql.connector.pooling config = { "host":"192.168.111.153", "port":"3306", "user":"quan", "password":"2004", "database":"mon" } try: pool = mysql.connector.pooling.MySQLConnectionPool( **config, pool_size = 10 ) #因为连接池里面的连接不需要关闭，所以这里不用使用finaly con = pool.get_connection() con.start_transaction() cursor = con.cursor() sql = "UPDATE class SET classname = %s WHERE id = %s" cursor.execute(sql,("javagai",2)) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e)
import mysql.connector.pooling config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } try: pool = mysql.connector.pooling.MySQLConnectionPool( **config, pool_size = 10 ) con = pool.get_connection() con.start_transaction() cursor = con.cursor() sql = "DELETE class,user FROM class LEFT JOIN user ON class.id = user.cid " cursor.execute(sql) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e)
结果：
import mysql.connector.pooling config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } try: pool = mysql.connector.pooling.MySQLConnectionPool( **config, pool_size=10 ) con = pool.get_connection() con.start_transaction() cursor = con.cursor() sql = "INSERT INTO class VALUES (%s ,%s)" sql_list = [[1,"QQQ"],[2,"ZZZ"],[3,"QQQ"]] cursor.executemany(sql,sql_list) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e)
结果；
实践111111111111111
将class_type表里面pid大于平均pid的信息导入到class_type_new里面，并将信息里面的name改为END
import mysql.connector.pooling config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } try: pool = mysql.connector.pooling.MySQLConnectionPool( **config, pool_size=10 ) con = pool.get_connection() con.start_transaction() cursor = con.cursor() sql = "DROP TABLE class_type_new" cursor.execute(sql) #创建的新表 sql = "CREATE TABLE class_type_new LIKE class_type" cursor.execute(sql) #获取平均id sql = "SELECT AVG(pid) AS avg FROM class_type" cursor.execute(sql) temp = cursor.fetchone() avg = temp[0]#平均id保存到变量 #查找大于平均id的id sql = "SELECT id FROM class_type WHERE pid >=%s " cursor.execute(sql,(avg,)) temp1 = cursor.fetchall()#结果保留到变量[(5,), (6,), (7,), (8,), (9,), (10,)] #将原来表里面符合平均id的复制到新表 do_id = "" for num in range(len(temp1)): id = str(temp1[num][0]) if num != len(temp1) - 1: id = id + "," do_id += id sql = "INSERT INTO class_type_new SELECT * FROM class_type WHERE id IN (" + do_id +")" cursor.execute(sql) #将原来的表删除 sql = "DELETE FROM class_type WHERE id IN (" + do_id + ")" cursor.execute(sql) #将软来的name改为END sql = "UPDATE class_type_new SET name = %s" cursor.execute(sql,("END",)) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e)
结果:
实践22222222 往class_type_new里面加入两组数据，其中 id 为最大id+10
不能再本表查询结果作为本表插入数据
import mysql.connector.pooling config = { "host":"192.168.111.153", "port":3306, "user":"quan", "password":"2004", "database":"mon" } try: pool = mysql.connector.pooling.MySQLConnectionPool( **config, pool_size=10 ) con = pool.get_connection() con.start_transaction() sql = "INSERT INTO class_type_new (" \ "SELECT MAX(id) + 10,%s,%s FROM class_type_new) UNION " \ "(SELECT MAX(id) + 20,%s,%s FROM class_type_new)" cursor = con.cursor() cursor.execute(sql,("DD",8,"EE",9)) con.commit() except Exception as e: if "con" in dir(): con.rollback() print(e)
结果：
