<small id='Om4ELinMZ'></small> <noframes id='a5TLbrvshk'>

  • <tfoot id='YrxzMLK'></tfoot>

      <legend id='59MnpYL'><style id='upashxBFy'><dir id='XwRJ'><q id='yPFm'></q></dir></style></legend>
      <i id='u7Fgno'><tr id='6zZt'><dt id='YTh4nCzR'><q id='Jscmf'><span id='2Zp96Osv'><b id='OvPgFKtmE'><form id='Nt4wqX'><ins id='KHF9jikOTS'></ins><ul id='moi08d25k'></ul><sub id='5El6d'></sub></form><legend id='DLty'></legend><bdo id='MjaoOueA'><pre id='ju2G4goMvR'><center id='wBXevYHWp'></center></pre></bdo></b><th id='wWMHABkiF'></th></span></q></dt></tr></i><div id='5TxaF'><tfoot id='2G3VD0Cb'></tfoot><dl id='QcYtAM'><fieldset id='DZU0bq9K'></fieldset></dl></div>

          <bdo id='SMzInihZg'></bdo><ul id='4QS8KBz'></ul>

          1. <li id='P9S40rql2'></li>
            登陆

            1号站平台开户-Python衔接MySQL数据库办法介绍(超具体!手把手项目事例操作)

            admin 2019-09-06 219人围观 ,发现0个评论

            作者 | CDA数据剖析师

            来历 | CDA数据剖析研究院

            本文涉及到的开发环境:

            • 操作体系 Windows 10
            • 数据库 MySQL 8.0
            • Python 3.7.2
            • pip 19.0.3

            两种办法进行数据库的衔接分别是PyMySQLmysql.connector

            过程

            1. 衔接数据库
            2. 生成游标目标
            3. 履行SQL句子
            4. 封闭游标
            5. 封闭衔接

            PyMySQL

            PyMySQL : 是封装了MySQL驱动的Python驱动,一个能使Python衔接到MySQL的库

            环境要求:Python version >= 3.4

            PyMySQL装置

            装置

            Win键+X键再按I键,调出Windows PowerShell窗口,输入

            pip install PyMySQL

            回车

            运转成果如下则装置成功

            pip version ===19.0.3

            检查版别

            检查PyMySQL的版别,输入

            pip show PyMySQL

            回车

            运用PyMySQL衔接数据库

            首要咱们的MySQL数据库已装置,且已建好名为test的数据库,其间有名为student的表

            import pymysql

            #衔接数据库

            conn=pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            sql="select * from `student` " # SQL句子

            cur.execute(sql) # 履行SQL句子

            data = cur.fetchall() # 经过fetchall办法取得数据

            for i in data[:2]: # 打印输出前2条数据

            print (i)

            cur.close() # 封闭游标

            conn.close() # 封闭衔接

            上述代码中,完成了经过Python衔接MySQL查询一切的数据,并输出前2条数据的功用。履行成果如下:

            ('a', '赵大', '16')

            ('b', '钱二', '16')

            mysql.connector

            mysql-connector-python:是MySQL官方的纯Python驱动;

            mysql.connector装置

            装置

            pip install mysql

            检查版别

            pip show mysql

            运用 mysql.connector衔接数据库

            首要咱们的MySQL数据库已装置,且已建好名为test的数据库,其间有名为student的表

            import mysql.connector

            conn=mysql.connector.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            sql="select * from `student` " # SQL句子

            cur.execute(sql) # 履行SQL句子

            data = cur.fetchall() # 经过fetchall办法取得数据

            for i in data[:2]: # 打印输出前2条数据

            print (i)

            cur.close() # 封闭游标

            conn.close() # 封闭衔接

            上述代码中,完成了经过Python衔接MySQL查询一切的数据,并输出前2条数据的功用。履行成果如下:

            ('a', '赵大', '16')

            ('b', '钱二', '16')

            Python对MySql数据库完成增修改查

            接下来咱们以用pymysql包为例,介绍一下怎么用Python对数据库中的数据进行增修改查 。

            import pymysql

            #衔接数据库

            conn=pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            #=============刺进句子===============================

            sql= "INSERT INTO student VA丑福晋LUES ('p','魏六','17')"

            #===================================================

            try:

            cur.execute(sql1) # 履行刺进的sql句子

            conn.commit() # 提交到数据库履行

            except:

            coon.rollback()# 假如发作过错则回滚

            conn.close() # 封闭数据库衔接

            然后咱们再运转查询句子

            import mysql.connector

            conn=mysql.connector.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            sql="select * from `student` " # SQL句子

            cur.execute(sql) # 履行SQL句子

            data = cur.fetchall() # 经过fetchall办法取得数据

            for i in data[:]: # 打印输出一切数据

            print (i)

            cur.close() # 封闭游标

            conn.close() # 封闭衔接

            履行成果便是

            ('b', '钱二', '16')

            ('c', '张三', '17')

            ('d', '李四', '17')

            ('e', '王五', '16')

            ('a', '赵大', '16')

            ('p', '魏六', '17')

            import pymysql

            #衔接数据库

            conn=pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            #=============删去句子===============================

            sql = "DELETE FROM student WHERE `学号` = "a"

            #===================================================

            try:

            cur.execute(sql) # 履行刺进的sql句子

            conn.commit() # 提交到数据库履行

            except:

            coon.rollback()# 假如发作过错则回滚

            conn.close() # 封闭数据库衔接

            import pymysql

            #衔接数据库

            conn=pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            #=============删去句子===============================

            sql ="UPDATE student SET `学员名字` = '欧阳' WHERE `学号` = 'b' "

            #===================================================

            try:

            cur.execute(sql) # 履行刺进的sql句子

            conn.commit() # 提交到数据库履行

            except:

            coon.rollback()# 假如发作过错则回滚

            conn.close() # 封闭数据库衔接

            import pymysql

            #衔接数据库

            conn=pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='pass1号站平台开户-Python衔接MySQL数据库办法介绍(超具体!手把手项目事例操作)word' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test' # 数据库称号

            ,charset='utf8' # 字符编码

            )

            cur = conn.cursor() # 生成游标目标

            #=============删去句子===============================

            sql="select * from `student` " # SQL句子

            #====================================================

            try:

            cur.execute(sql) # 履行刺进的sql句子

            data = cur.fetchall()

            for i in data[:]:

            print (i)

            conn.commit() # 提交到数据库履行

            except:

            coon.rollback()# 假如发作过错则回滚

            conn.close() # 封闭数据库衔接

            小型事例

            import pymysql

            config = {

            'host': '127.0.0.1',

            'port': 3306,

            'user': 'root',

            'passwd': 'password',

            'charset':'utf8',

            }

            conn = pymysql.connect(**config)

            cursor = conn.cursor()

            try:

            # 创立数据库

            DB_NAME = 'test_3'

            cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)

            cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)

            conn.select_db(DB_NAME)

            #创立表

            TABLE_NAME = 'bankData'

            cursor.execute('CREATE TABLE %s(id int primary key,money int(30))' %TABLE_NAME)

            # 批量刺进纪录

            values = 1号站平台开户-Python衔接MySQL数据库办法介绍(超具体!手把手项目事例操作)[]

            for i in range(20):

            values.append((int(i),int(156*i)))

            cursor.executemany('INSERT INTO bankData values(%s,%s)',values)

            conn.commit()

            # 查询数据条目

            count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)

            print ('total records:{}'.format(cursor.rowcount))

            # 获取表名信息

            desc = cursor.description

            print ("%s %3s" % (desc[0][0], desc[1][0]))

            cursor.scroll(10,mode='absolute')

            results = cursor.fetchall()

            for result in results:

            print (result)

            except:

            import traceback

            traceback.print_exc()

            # 发作过错时会滚

            conn.rollback()

            finally:

            # 封闭游标衔接

            cursor.close()

            # 封闭数据库衔接

            conn.close()

            归纳事例

            FIFA球员信息体系

            from pymysql import *

            class Mysqlpython:

            def __init__(self, database='test', host='127.0.0.1', user="root",

            password='password', port=3306, charset="utf8"):

            self.host = host

            self.user = user

            self.pass1号站平台开户-Python衔接MySQL数据库办法介绍(超具体!手把手项目事例操作)word = password

            self.port = port

            self.database = database

            self.charset = charset

            # 数据库衔接办法:

            def open(self):

            self.db = connect(host=self.host, user=self.user,

            password=self.password, port=self.port,

            database=self.database,

            charset=self.charset)

            # 游标目标

            self.cur = self.db.cursor()

            # 数据库封闭办法:

            def close(self):

            self.cur.close()

            self.db.close()

            # 数据库履行操作办法:

            def Operation(self, sql):

            try:

            self.open()

            self.cur.execute(sql)

            self.db.commit()

            print("ok")

            except Exception as e:

            self.db.rollback()

            print("Failed", e)

            self.close()

            # 数据库查询一切操作办法:

            def Search(self, sql):

            try:

            self.open()

            self.cur.execute(sql)

            result = self.cur.fetchall()

            return result

            except Exception as e:

            print("Failed", e)

            self.close()

            def Insert():#怎么从外面将数据录入到sql句子中

            ID = int(input("请输入球员编号:"))

            people_name = input("请输入球员名字:")

            PAC = int(input("请输入速度评分:"))

            DRI = int(input("请输入盘带评分:"))

            SHO = int(input("请输入射门评分:"))

            DEF = int(input("请输入防卫评分:"))

            PAS = int(input("请输入传球评分:"))

            PHY = int(input("请输入身体评分:"))

            score =(PAC+DRI+SHO+DEF+PAS+PHY)/6

            sql_insert = "insert into FIFA(ID, people_name, PAC,DRI,SHO,DEF, PAS, PHY, score) values(%d,'%s',%d,%d,%d,%d,%d,%d,%d)"%(ID, people_name, PAC,DRI,SHO,DEF, PAS, PHY, score)

            print(people_name)

            return sql_insert

            def Project():

            print("球员的才能评分有:")

            list=['速度','盘带','射门','防卫','传球','身体','归纳']

            print(list)

            def Exit():

            print("欢迎下次运用!!!")

            exit()

            def Search_choice(num):

            date = Mysqlpython()

            date.open()

            if num=="2":

            # 1.增加操作

            sql_insert = Insert()

            date.Operation(sql_insert)

            print("增加成功!")

            Start()

            elif num=="1":

            # 2.查找数据,其间order by 是为了按什么次序输出,asc 是升序输出,desc降序输出

            input_date=input("请挑选您想要以什么格局输出:默许升序摆放1.球员编号,2.速度,3.盘带,4.射门, 5.防卫, 6.传球, 7.身体 , 8.归纳 ")

            if input_date=="1":

            sql_search = "select * from FIFA order by ID asc"

            elif input_date=="2":

            sql_search = "select * from FIFA order by PAC asc"

            elif input_date=="3":

            sql_search = "select * from FIFA order by DRI asc"

            elif input_date=="4":

            sql_search = "select * from FIFA order by SHO asc"

            elif input_date=="5":

            sql_search = "select * from FIFA order by DEF asc"

            elif input_date=="6":

            sql_search = "select * from FIFA order by PAS asc"

            elif input_date=="7":

            sql_search = "select * from FIFA order by PHY asc"

            elif input_date=="8":

            sql_search = "select * from FIFA order by PHY score"

            else:

            print("请从头输入!")

            result = date.Search(sql_search)

            print(" 编号 名字 速度 盘带 射门 防卫 传球 身体 归纳 ")

            for str in result:

            print(str)

            Start()

            elif num=="3":

            Project()

            Start()

            elif num=="4":

            del_num=input("请输入您要删去球员的编号:")

            sql_delete="delete from FIFA where id=%s"%del_num

            date.Operation(sql_delete)

            print("删去成功!")

            Start()

            elif num=="5":

            Exit()

            else:

            print("输入有误,请从头输入!")

            def Start():

            print("********************************************************")

            print("* 欢迎来到FIFA球员信息体系 *")

            print("*1.检查球员信息 2.球员信息录入 *")

            print("*3.球员才能 4.删去球员信息 *")

            print("*5.退出体系 *")

            print("********************************************************")

            choice = input("请输入您的挑选:")

            Search_choice(choice)

            if __name__=="__main__":

            Start()

            银行转账体系

            先树立数据库test_3和表bankdata

            import pymysql

            config = {

            'host': '127.0.0.1',

            'port': 3306,

            'user': 'root',

            'passwd': 'password',

            'charset':'utf8',

            }

            conn = pymysql.connect(**config)

            cursor = conn.cursor()

            try:

            # 创立数据库

            DB_NAME = 'test_3'

            cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)

            cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)

            conn.select_db(DB_NAME)

            #创立表

            TABLE_NAME = 'bankData'

            cursor.execute('CREATE TABLE %s(id int primary key,money int(30))' %TABLE_NAME)

            # 批量刺进纪录

            values = []

            for i in range(20):

            values.append((int(i),int(156*i)))

            cursor.executemany('INSERT INTO bankData values(%s,%s)',values)

            conn.commit()

            # 查询数据条目

            count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)

            print ('total records:{}'.format(cursor.rowcount))

            # 获取表名信息

            desc = cursor.description

            print ("%s %3s" % (desc[0][0], desc[1][0]))

            cursor.scroll(10,mode='absolute')

            results = cursor.fetchall()

            for result in results:

            print (result)

            except:

            import traceback

            traceback.print_exc()

            # 发作过错时会滚

            conn.rollback()

            finally:

            # 封闭游标衔接

            cursor.close()

            # 封闭数据库衔接

            conn.close()

            构建体系

            import pymysql

            class TransferMoney(object):

            # 结构办法

            def __init__(self, conn):

            self.conn = conn

            self.cur = conn.cursor()

            def transfer(self, source_id, target_id, money):

            if not self.check_account_avaialbe(source_id):

            raise Exception("账户不存在")

            if not self.check_account_avaialbe(target_id):

            raise Exception("账户不存在")

            if self.has_enough_money(source_id, money):

            try:

            self.reduce_money(source_id, money)

            self.add_money(target_id, money)

            except Exception as e:

            print("转账失利:", e)

            self.conn.rollback()

            else:

            self.conn.commit()

            print("%s给%s转账%s金额成功" % (source_id, target_id, money))

            def check_account_avaialbe(self, acc_id):

            """判别帐号是否存在, 传递的参数是银行卡号的id"""

            select_sqli = "select * from bankData where id=%d;" % (acc_id)

            print("execute sql:", select_sqli)

            res_count = self.cur.execute(select_sqli)

            if res_count == 1:

            return True

            else:

            # raise Exception("账户%s不存在" %(acc_id))

            return False

            def has_enough_money(self, acc_id, money):

            """判别acc_id账户上金额> money"""

            # 查找acc_id存储金额?

            select_sqli = "select money from bankData where id=%d;" % (acc_id)

            print("execute sql:", select_sqli)

            self.cur.execute(select_sqli) # ((1, 500), )

            # 获取查询到的金额钱数;

            acc_money = self.cur.fetchone()[0]

            # 判别

            if acc_money >= money:

            return True

            else:

            return False

            def add_money(self, acc_id, money):

            update_sqli = "update bankData set money=money+%d where id=%d" % (money, acc_id)

            print("add money:", update_sqli)

            self.cur.execute(update_sqli)

            def reduce_money(self, acc_id, money):

            update_sqli = "update bankData set money=money-%d where id=%d" % (money, acc_id)

            print("reduce money:", update_sqli)

            self.cur.execute(update_sqli)

            # 析构办法

            def __del__(self):

            self.cur.close()

            self.conn.close()

            if __name__ == '__main__':

            # 1. 衔接数据库,

            conn = pymysql.connect(host = '127.0.0.1' # 衔接称号,默许127.0.0.1

            ,user = 'root' # 用户名

            ,passwd='password' # 暗码

            ,port= 3306 # 端口,默许为3306

            ,db='test_3' # 数据库称号

            ,charset='utf8'

            ,autocommit=True, # 假如刺进数据,主动提交给数据库

            )

            trans = TransferMoney(conn)

            trans.transfer(15, 12, 200)

            请关注微信公众号
            微信二维码
            不容错过
            Powered By Z-BlogPHP