linux python 操作mysql_python19 操作mysql

connect 模块下载

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 incursor:

print(type(one))

print(one[0],one[1])

sql2= "SHOW TABLES"cursor.execute(sql2)for i incursor:

print(i)

结果；

1dada

2jave

4py

('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.connectortry:

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 Exceptionase:if "con" indir():

con.rollback()

print(e)finally:if "con" indir():

con.close()

结果：

import mysql.connectortry:

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 Exceptionase:if "con" indir():

con.rollback()

print(e)finally:if "con" indir():

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 Exceptionase:if "con" indir():

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 Exceptionase:if "con" indir():

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 Exceptionase:if "con" indir():

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 inrange(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 Exceptionase:if "con" indir():

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 Exceptionase:if "con" indir():

con.rollback()

print(e)

结果：