python中MySQL数据库相关操作
一 安装基本环境
1 简介
2 安装MySQL数据库
1 挂载镜像文件(本次使用的是本地镜像文件)2 启动MySQL数据库3 创建用户名和密码并刷新
4 修改默认字符集:
5 查看字符集是否加载完毕
6 创建数据库二 MySQL-python1 安装1 安装
2 连接一般流程
2 MySQL链接基本操作及说明1 导入MySQLdb 模块
2 创建连接
3 初始化游标4 使用游标.execute(‘sql‘)语句负责向MySQL数据库传递消息。
5 提交
6 关闭链接
7 查看
3 数据库进阶1 显示设置
1 进行查看显示
2 显示所有查看的结果3 显示指定的数量4 进行查看使用cur.scroll(0,‘absolute‘)
2 多行插入1 使用for 循环遍历的方式插入:
2 将sql 语句与cur.execute 分离的方式插入
3 进行多行插入
4 :数据库应用:1 生成姓名
2 判断数据库的某个表是否存在3 应用封装mysql数据库的类
三 pymysql1 安装pymysql1 安装pymysql 是第三方模块库,需要安装 pip install pymysql 2 参数简介和基本链接1 基本参数详解
def __init__(self,host=None,user=None,password="",database=None,port=0,unix_socket=None,charset=‘‘,sql_mode=None,read_default_file=None,conv=None,use_unicode=None,client_flag=0,cursorclass=Cursor,init_command=None,connect_timeout=10,ssl=None,read_default_group=None,compress=None,named_pipe=None,autocommit=False,db=None,passwd=None,local_infile=False,max_allowed_packet=16*1024*1024,defer_connect=False,auth_plugin_map=None,read_timeout=None,write_timeout=None,bind_address=None,binary_prefix=False,program_name=None,server_public_key=None):
def ping(self,reconnect=True): """ Check if the server is alive. :param reconnect: If the connection is closed,reconnect. :raise Error: If the connection is closed and reconnect=False. """ if self._sock is None: if reconnect: self.connect() reconnect = False else: raise err.Error("Already closed") try: self._execute_command(COMMAND.COM_PING,"") self._read_ok_packet() except Exception: if reconnect: self.connect() self.ping(False) else: raise 2 conn.ping 相关测试#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None try: conn=Connection(‘192.168.1.200‘,‘test‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) finally: if conn: print(‘关闭链接:‘,conn.ping(False)) conn.close()
3 游标 Cursor
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor() sql="insert into t(id,username,password) values(3,‘mysql‘,‘mysql‘)" line=cursor.execute(sql) # 此处未提交事务。若关闭,则直接导致事务回滚 print (line) cursor.close() finally: if conn: print(‘关闭链接:‘,conn.ping(False)) conn.close()
4 添加事务管理#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘mysql‘)" line=cursor.execute(sql) # 此处未提交事务。若关闭,则直接导致事务回滚 print (line) cursor.close() # 若没有异常,则提交事务 conn.commit() except: # 若存在异常,则回滚事务 conn.rollback() finally: if conn: print(‘关闭链接:‘,conn.ping(False)) conn.close()
5 批量增加数据#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor() for i in range(5): sql="insert into t(id,‘mysql‘)" line=cursor.execute(sql) print (line) # 此处未提交事务。若关闭,则直接导致事务回滚 # 若没有异常,则提交事务 conn.commit() except: # 若存在异常,则回滚事务 conn.rollback() finally: if cursor: cursor.close() if conn: conn.close()
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor() for i in range(10,15): sql="insert into t(id,password) values({},‘mysql‘)".format(i) line=cursor.execute(sql) # 此处未提交事务。若关闭,则直接导致事务回滚 # 若没有异常,则提交事务 conn.commit() except: # 若存在异常,则回滚事务 conn.rollback() finally: if cursor: cursor.close() if conn: conn.close()
6 查询返回结果1 概念
2 普通元祖返回#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor() sql="select * from t" line=cursor.execute(sql) print (‘获取一个‘,cursor.fetchone()) # 获取一个 print (‘获取下面两个‘,cursor.fetchmany(2)) # 获取下面两个 print (‘获取所有‘,cursor.fetchall()) # 获取所有 cursor.rownumber=0 # 游标初始化,支持负索引,当大于len的索引,则会不存在 # 此处未提交事务。若关闭,则直接导致事务回滚 # 若没有异常,则提交事务 print (‘获取所有‘,cursor.fetchall()) # 获取所有 print (‘获取一个‘,cursor.fetchone()) #此处无法获取到了 conn.commit() finally: if cursor: cursor.close() if conn: conn.close()
3 字典返回(字典游标)
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor(DictCursor) sql="select * from t" line=cursor.execute(sql) print (cursor.fetchall()) # 获取一个 finally: if cursor: cursor.close() if conn: conn.close()
7 SQL 注入1 概念
2 常用的SQL注入方式1 数字拼接#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 cursor=conn.cursor() sql="select * from t where id={}".format(‘5 or 1=1‘) print (sql) line=cursor.execute(sql) print (cursor.fetchall()) # 获取一个 finally: if cursor: cursor.close() if conn: conn.close()
2 字符串拼接#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) print (conn.ping(False)) # 获取游标 name="‘test‘" passwd="‘test‘ or 1=1" cursor=conn.cursor() sql="select * from t where username={} and password ={}".format(name,passwd) print (sql) line=cursor.execute(sql) print (cursor.fetchall()) # 获取一个 finally: if cursor: cursor.close() if conn: conn.close()
3 解决SQL 注入方式1 概念
2 查询效率高原因
3 具体代码
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) d={‘name‘:"‘test‘",‘passwd‘:"‘test‘ or 1=1"} cursor=conn.cursor() sql="select * from t where username=%(name)s and password=%(passwd)s" line=cursor.execute(sql,d) print (cursor.fetchall()) # 获取一个 finally: if cursor: cursor.close() if conn: conn.close()
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) d={‘id‘: ‘1 or 1=1‘} cursor=conn.cursor() sql="select * from t where id=%(id)s" line=cursor.execute(sql,d) print (cursor.fetchall()) # 获取一个 finally: if cursor: cursor.close() if conn: conn.close()
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) cursor=conn.cursor() L1=[ (i,‘admin‘,‘admin‘) for i in range(20,23)] for x in L1: sql="insert into t(id,password) values(%s,%s,%s)" line=cursor.execute(sql,x) print (line) conn.commit() finally: if cursor: cursor.close() if conn: conn.close()
#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) cursor=conn.cursor() L1=[ [i,‘admin‘] for i in range(24,26)] for x in L1: sql="insert into t(id,x) conn.commit() finally: if cursor: cursor.close() if conn: conn.close()
8 上下文支持1 查看连接和游标部分源码1 连接上下文相关源码如下:def __enter__(self): """Context manager that returns a Cursor""" warnings.warn( "Context manager API of Connection object is deprecated; Use conn.begin()",DeprecationWarning) return self.cursor() def __exit__(self,exc,value,traceback): """On successful exit,commit. On exception,rollback""" if exc: self.rollback() else: self.commit()
2 游标相关源码def __enter__(self): return self def __exit__(self,*exc_info): del exc_info self.close()
2 连接相关上下文代码如下#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) with conn as cursor: #此处返回一个cursor链接 sql="select * from t" cursor.execute(sql) print (cursor.fetchmany(5)) finally: if cursor: #上述的代码中未使用cursor的上下文,因此此处还是需要的 cursor.close() if conn: conn.close()
3 游标相关代码如下#!/usr/bin/poython3.6 #conding:utf-8 from pymysql.connections import Connection from pymysql.cursors import DictCursor conn=None cursor=None try: conn=Connection(‘192.168.1.200‘,‘[email?protected]‘,‘test‘) with conn as cursor: #此处返回一个cursor链接 with cursor: #此处使用cursor的上下文,默认会关闭其游标 sql="select * from t" cursor.execute(sql) print (cursor.fetchmany(5)) finally: if conn: conn.close()
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |