mysql

Windows

Ubuntu

import pymysql

connect()

conn = connect(参数列表)

cursor()

cursor1 = conn.cursor()

mode

mode

relative

value

value

value

mode

absolute

# 导入python操作mysql的模块
import pymysql
<h1>获取连接对象</h1><p>conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')</p><h1>获取执行工具</h1><p>cur = conn.cursor()</p><h1>sql语句,增删改</h1><h1>sql = 'select birthday from t_user'</h1><p>sql = 'select id,name,pwd,birthday from t_user'</p><h1>执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数</h1><p>count = cur.execute(sql)
print('查询的结果有%s条数据'%count)</p><h1>获取第一行</h1><p>dateOne = cur.fetchone()
print(dateOne)</p><h1>向上移动一行</h1><p>cur.scroll(-1)</p><h1>向下移动一行</h1><p>cur.scroll(1)</p><p>cur.scroll(1,mode='absolute')   # 绝对的，这里指的是第一行
cur.scroll(1,mode='relative')   # 相对的</p><h1>获取所有行的数据</h1><p>dataAll = cur.fetchall()
print(dataAll)</p><p>for temp in dataAll:
print(temp)
print(dataAll[-1][2])      #dataAll[-1]得到的是一个用户所有的信息，dataAll[-1][2]获取最后一个人的密码</p><p>for temp in cur:
print(temp)</p><p>s = 'id:%s,name:%s,pwd:%s,birthday:%s'
for temp in dataAll:
print(s%(temp[0],temp[1],temp[2],temp[3]))</p><h1>关闭</h1><p>cur.close()
conn.close()

testInsert.py

#encoding=utf-8
import pymysql</p><p>try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("insert into students(sname) values('张良')")
print(count)
conn.commit()
cs1.close()
conn.close()
except Exception as e:
print(e)

testUpdate.py

#encoding=utf-8
import pymysql</p><p>try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("update students set sname='刘邦' where id=6")
print(count)
conn.commit()
cs1.close()
conn.close()
except Exception as e:
print(e)

testDelete.py

#encoding=utf-8
import pymysql</p><p>try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cs1=conn.cursor()
count=cs1.execute("delete from students where id=6")
print(count)
conn.commit()
cs1.close()
conn.close()
except Exception as e:
print(e)

testSelectOne.py

import pymysql</p><p>try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cur=conn.cursor()
cur.execute('select * from students where id=7')
result=cur.fetchone()
print(result)
cur.close()
conn.close()
except Exception as e:
print(e)

testSelectMany.py

#encoding=utf8
import pymysql</p><p>try:
conn=pymysql.connect(host='localhost',port=3306,db='test1',user='root',passwd='mysql',charset='utf8')
cur=conn.cursor()
cur.execute('select * from students')
result=cur.fetchall()
print(result)
cur.close()
conn.close()
except Exception as e:
print(e)

# 导入python操作mysql的模块
import pymysql
import time</p><h1>获取连接对象</h1><p>conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')</p><h1>获取执行工具</h1><p>cur = conn.cursor()</p><h1>sql语句,增删改，sql注入</h1><p>sql = 'insert into t_user(name,pwd,birthday) values(%s,%s,%s)'</p>
                    <div class="aritcle_card">
                        <a class="aritcle_card_img" href="/ai/%E4%BA%91%E9%9B%80%E8%AF%AD%E8%A8%80%E6%A8%A1%E5%9E%8B">
                            <img src="https://img.php.cn/upload/ai_manual/000/000/000/175680205024673.png" alt="云雀语言模型">
                        </a>
                        <div class="aritcle_card_info">
                            <a href="/ai/%E4%BA%91%E9%9B%80%E8%AF%AD%E8%A8%80%E6%A8%A1%E5%9E%8B">云雀语言模型</a>
                            <p>云雀是一款由字节跳动研发的语言模型，通过便捷的自然语言交互，能够高效的完成互动对话</p>
                            <div class="">
                                <img src="/static/images/card_xiazai.png" alt="云雀语言模型">
                                <span>54</span>
                            </div>
                        </div>
                        <a href="/ai/%E4%BA%91%E9%9B%80%E8%AF%AD%E8%A8%80%E6%A8%A1%E5%9E%8B" class="aritcle_card_btn">
                            <span>查看详情</span>
                            <img src="/static/images/cardxiayige-3.png" alt="云雀语言模型">
                        </a>
                    </div>
                <h1>参数列表</h1><p>name = input('输入姓名:')
pwd = input('输入密码:')
birthday = input('输入生日:')     # 2017年10月01日-->日期struct_time(--->2017-10-01)
birthday = time.strptime(birthday,'%Y年%m月%d日')      #这里我们就用到了时间与字符串的相互转换(详情见MySQL高级)
params = [name,pwd,birthday]</p><h1>执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数</h1><p>count = cur.execute(sql,params)</p><h1>提交</h1><p>conn.commit()
print('受影响的行数:%s'%count)</p><h1>关闭</h1><p>cur.close()
conn.close()

# 导入python操作mysql的模块
import pymysql</p><p>try:
conn = None
cur = None</p><pre class="brush:php;toolbar:false;"><pre class="brush:php;toolbar:false;"># 获取连接对象
conn = pymysql.connect(host='127.0.0.1',
                       user='root',
                       password='123456',
                       database='python01',
                       port=3306,
                       charset='utf8')

# 模拟异常
# a = 1 / 0

# 获取执行工具
cur = conn.cursor()

# sql语句,增删改
sql = 'insert into t_user(name,pwd,birthday) values("小伊","123456",str_to_date("2017年10月20日","%Y年%m月%d日"))'

# 执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数
count = cur.execute(sql)

# 提交
conn.commit()
print('受影响的行数:%s' % count)

<pre class="brush:php;toolbar:false;">print(str(ex))
# 将异常继续抛出
# raise

# 导入python操作mysql的模块
import pymysql</p><h1>获取连接对象</h1><p>conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='python01', port=3306, charset='utf8')</p><h1>获取执行工具</h1><p>cur = conn.cursor()</p><h1>sql语句,增删改</h1><h1>sql = 'select birthday from t_user'</h1><p>sql = 'select id,name,pwd,birthday from t_user'</p><h1>执行,返回值。如果是增删改，返回受影响的行数，如果是查询，返回查询的行数</h1><p>count = cur.execute(sql)
print('查询的结果有%s条数据'%count)</p><h1>获取第一行</h1><h1>dateOne = cur.fetchone()</h1><h1>print(dateOne)</h1><h1>for temp in cur:</h1><h1>print(temp)</h1><p>s = 'id:%s,name:%s,pwd:%s,birthday:%s'
for temp in dataAll:
print(s%(temp[0],temp[1],temp[2],temp[3]))</p><h1>关闭</h1><p>cur.close()
conn.close()

mySqlHelper

# python操作mysql进行增删改查的封装</p><h1>1、增删改，代码类似</h1><h1>2、查询</h1><h1>代码分析</h1><h1>1、获取连接对象</h1><h1>2、sql语句不同，参数不同</h1><h1>3、获取执行对象</h1><h1>增删改</h1><h1>查询</h1><h1>1、fetchone</h1><h1>2、fetchall</h1><h1>4、处理结果</h1><h1>5、关闭</h1><h1>面向对象  建立类，封装属性和函数</h1><p>import pymysql</p><p>class MysqlHelper:
'''python操作mysql的增删改查的封装'''</p><pre class="brush:php;toolbar:false;"><pre class="brush:php;toolbar:false;">def __init__(self, host, user, password, database, port=3306, charset='utf8'):
    '''
    初始化参数
    :param host:        主机
    :param user:        用户名
    :param password:    密码
    :param database:    数据库
    :param port:        端口号，默认是3306
    :param charset:     编码，默认是utf8
    '''
    self.host = host
    self.port = port
    self.database = database
    self.user = user
    self.password = password
    self.charset = charset

def connect(self):
    '''
    获取连接对象和执行对象
    :return:
    '''
    self.conn = pymysql.connect(host=self.host,
                                user=self.user,
                                password=self.password,
                                database=self.database,
                                port=self.port,
                                charset=self.charset)
    self.cur = self.conn.cursor()

def fetchone(self, sql, params=None):
    '''
    根据sql和参数获取一行数据
    :param sql:          sql语句
    :param params:       sql语句对象的参数元组，默认值为None
    :return:             查询的一行数据
    '''
    dataOne = None
    try:
        count = self.cur.execute(sql, params)
        if count != 0:
            dataOne = self.cur.fetchone()
    except Exception as ex:
        print(ex)
    finally:
        self.close()
    return dataOne

def fetchall(self, sql, params=None):
    '''
    根据sql和参数获取一行数据
    :param sql:          sql语句
    :param params:       sql语句对象的参数列表，默认值为None
    :return:             查询的一行数据
    '''
    dataall = None
    try:
        count = self.cur.execute(sql, params)
        if count != 0:
            dataall = self.cur.fetchall()
    except Exception as ex:
        print(ex)
    finally:
        self.close()
    return dataall

def __item(self, sql, params=None):
    '''
    执行增删改
    :param sql:           sql语句
    :param params:        sql语句对象的参数列表，默认值为None
    :return:              受影响的行数
    '''
    count = 0
    try:
        count = self.cur.execute(sql, params)
        self.conn.commit()
    except Exception as ex:
        print(ex)
    finally:
        self.close()
    return count

def update(self, sql, params=None):
    '''
    执行修改
    :param sql:     sql语句
    :param params:  sql语句对象的参数列表，默认值为None
    :return:        受影响的行数
    '''
    return self.__item(sql, params)

def insert(self, sql, params=None):
    '''
    执行新增
    :param sql:     sql语句
    :param params:  sql语句对象的参数列表，默认值为None
    :return:        受影响的行数
    '''
    return self.__item(sql, params)

def delete(self, sql, params=None):
    '''
    执行删除
    :param sql:     sql语句
    :param params:  sql语句对象的参数列表，默认值为None
    :return:        受影响的行数
    '''
    return self.__item(sql, params)

def close(self):
    '''
    关闭执行工具和连接对象
    '''
    if self.cur != None:
        self.cur.close()
    if self.conn != None:
        self.conn.close()</code></pre><ol><li>测试查询多条数据</li></ol><pre><code class="javascript">import mysqlHelper

import mysqlHelper</p><h1>初始化对象</h1><p>helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')</p><h1>连接</h1><p>helper.connect()</p><h1>sql</h1><p>sql = 'select * from t_user where id = %s'</p><h1>sql = 'select * from t_user where id = 1'</h1><h1>params</h1><p>params = [2]</p><h1>执行</h1><p>data = helper.fetchone(sql, params)</p><h1>data = helper.fetchone(sql)</h1><h1>判断</h1><p>if data:
print(data)
else:  # None,False,0
print('没有数据.')

import mysqlHelper
import time</p><h1>初始化对象</h1><p>helper = mysqlHelper.MysqlHelper('127.0.0.1', 'root', '123456', 'python01')</p><h1>连接</h1><p>helper.connect()</p><h1>sql</h1><p>sql = 'update t_user set name =%s,pwd=%s,birthday=%s where id=%s'</p><h1>params</h1><p>id = input('输入编号:')
name = input('输入姓名:')
pwd = input('输入密码:')
birthday = time.strptime(input('输入生日:'), '%Y年%m月%d日')
params = [name, pwd, birthday,id]</p><h1>执行</h1><p>count = helper.update(sql, params)</p><h1>判断</h1><p>if count:
print('操作成功.')
else:  # None,False,0
print('操作失败.')

create table userinfos(
id int primary key auto_increment,
uname varchar(20),
upwd char(40),
isdelete bit default 0);</p><p>/<em>ret = doPwd('123')
print(ret)
结果：40bd001563085fc35165329ea1ff5c5ecbdbbeef
</em>/</p><p>-- 插入如下数据，用户名为123,密码为123,这是sha1加密后的值
insert into userinfos values(1,'123','40bd001563085fc35165329ea1ff5c5ecbdbbeef',0);

from mysqlHelper import MysqlHelper
import hashlib</p><p>def login():
'''登录'''
name = input('输入用户名:')
pwd = input('输入密码:')</p><h1>加密</h1><pre class="brush:php;toolbar:false;"><pre class="brush:php;toolbar:false;">pwd = doPwd(pwd)
helper = MysqlHelper('127.0.0.1', 'root', '123456', 'python01')
helper.connect()
sql = 'select * from t_user where name=%s and pwd=%s'
params = [name, pwd]
data = helper.fetchone(sql, params)
if data:
    print('登录成功.')
else:  # None,False,0
    print('登录失败.')

<pre class="brush:php;toolbar:false;">pwd = doPwd(pwd)
helper = MysqlHelper('127.0.0.1', 'root', '123456', 'python01')
helper.connect()
sql = 'insert into t_user(name,pwd) values(%s,%s)'
params = [name, pwd]
count = helper.insert(sql, params)
if count:
    print('操作成功.')
else:  # None,False,0
    print('操作失败.')

<pre class="brush:php;toolbar:false;">login()</code></pre>