Python基础（二）--- IDEA中整合Python和MySQL，使用Python进行SQL操作_idea中写python mysql

作者：神奇cpp | 2024-08-14 19:27:10

踩

idea中写python mysql

一、Python操作MySQL
-----------------------------------------------------
    1.安装MySQL

    2.安装mysql的python模块
        a.下载并安装PyMySQL-master.zip
        https://github.com/PyMySQL/PyMySQL

    3.解压，在解压目录下，进入cmd，执行安装命令
        cmd> python setup.py install

    4.重启idea

    5.创建python文件，测试是否连接


import pymysql
 
        print("================测试是否连通====================")
        try:
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            cur = conn.cursor()
            cur.execute('select version()')
            version = cur.fetchone()
            print(version)
            cur.close()
            conn.close()
        except  Exception:
            print("发生异常")

        ================测试是否连通====================
        ('5.5.60',)


二、使用Python进行SQL操作
--------------------------------------------------------------------------
    1.create and Drop Table


# print("================ Drop Table====================")
        # try:
        #     conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
        #     cur = conn.cursor()
        #     cur.execute('drop table t1')
        #     cur.close()
        #     conn.close()
        # except  Exception:
        #     print("发生异常")
 
        # print("================ Create Table====================")
        # try:
        #     conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
        #     cur = conn.cursor()
        #     cur.execute('create table t1(id int primary key auto_increment, name varchar (20), age int)')
        #     conn.commit()
        #     cur.close()
        #     conn.close()
        # except  Exception:
        #     print("发生异常")

    2.insert into
        --------------------------------------------------------------------


print("================ Insert 插入 ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host = "localhost", user = "mysql", passwd= "mysql",db = "python",port = 3306,charset="utf8");
            # 打开游标
            cur = conn.cursor();
            # 开始插入
            sql = "insert into t1 (name,age) values ('%s','%d')" %('tom',12);
            cur.execute(sql);
            conn.commit();
            cur.close()
            conn.close()
 
        except Exception:
            print("发生异常")

        ----------------------------------------------------------------------------------------


print("================ Insert 插入10000条记录 ====================")
        print("================ 带提交和回滚机制 ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            i = 0 ;
            while i < 10000 :
                sql = "insert into t1(name,age) values('%s',%d)" % ("tom" + str(i),i % 100);
                #异常回滚测试
                if(i == 50):
                    sql = "insert"
                #执行sql插入
                cur.execute(sql)
                i += 1 ;
            #提交
            conn.commit()
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()
'运行

    3.delect from


 print("================ Delete====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            sql = "delete from t1 where id > 50";
            cur.execute(sql)
            #提交
            conn.commit()
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

    4.update set


print("================ Update ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            sql = "update t1 set age = age - 20 where age > 20";
            cur.execute(sql)
            #提交
            conn.commit()
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

    5.select from


 print("================ select from ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            sql = "select * from t1";
            cur.execute(sql)
            res = cur.fetchall()
            for r in res :
                print(str(r[0]) + "," + r[1] + "," + str(r[2]));
            #提交
            conn.commit()
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

    6.select count(*)


 print("================ select count(*) from ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            sql = "select count(*) from t1 where id > 10";
            cur.execute(sql)
            res = cur.fetchone()
            print(res[0])
            #提交
            conn.commit()
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

    7.调用Mysql中的函数


print("================ 调用Mysql的函数 ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='python', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #执行sql
            sql = "call mydata.up_add(1,2,@q);";
            cur.execute(sql)
            cur.execute("select @q");
            res = cur.fetchone()
            print(res[0])
            conn.commit()
            #提交
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

    8.调用Mysql中的存储过程


 print("================ 调用Mysql的存储过程 ====================")
        try:
            # 开启连接
            conn = pymysql.connect(host='localhost', user='mysql', passwd='mysql', db='mydata', port=3306, charset='utf8')
            # 打开游标
            cur = conn.cursor()
            # 关闭自动提交
            conn.autocommit(False);
            #创建表
            sql = "create table mytable(id int primary key auto_increment, name varchar(20), password varchar(20), age int)";
            cur.execute(sql)
            #执行存储过程
            cur.execute("call big_insert2(100)");
            #查询
            sql = "select * from mytable";
            cur.execute(sql)
 
            res = cur.fetchall()
            for r in res:
                print(str(r[0]) + "," + str(r[1]) + "," +str(r[2]) + "," +str(r[3]))
            conn.commit()
            #提交
 
        except Exception:
            print("发生异常,进行回滚操作")
            conn.rollback()
 
        finally:
            #关闭
            cur.close()
            conn.close()

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/神奇cpp/article/detail/980499