python+pymysql:连接数据库
""" 1、pymysql安装导入: 1.1、在cmd命令行输入pip install pymysql或者pip3 install PyMySQL安装 1.2、在pycharm里安装: 1.2.1、点击最底部Terminal, 输入pip install pymysql或者pip3 install PyMySQL安装 1.2.2、点击最顶部File》Settings(快捷键Ctrl + Alt + S)》Project: PyProject》PythonInterpreter》点击 + 号》搜索输入pymysql》选择对应模块》点击InstallPackage安装模块 1.3、模块导入:import pymysql 2、输入命令安装如果失败: 2.1、检查一下pip是否安装或升级pip 2.2、检查是否有开启网络代理比如fiddler,如果有关闭fiddler代理 2.3、检查python环境是否正确 3、pymysql连接数据库和使用: """ # -*- coding: UTF-8 -*- # 当前用户的登录名:铺先生技术研发中心 # 当前文件名:Mysql_家政.py # 创建时间:2023-03-15 08:56:41 # 创建人:猪油仔 import pymysql db = pymysql.connect( host='000.0.0.0', # 数据库地址 port=0000, # 端口 user='0000', # 用户名/账号 password='0000', # 密码 db='0000', # 数据库名 charset='utf8' # 数据库编码 ) """使用cursor()方法获取操作游标""" cursor = db.cursor() print("---------------------------------------------------------------------------------------------------------------") """查询数据库版本""" sql_01 = "select version();" """查询所有表名称""" sql_02 = "show tables;" """创建表""" sql_03 = """ CREATE TABLE if not exists test1_python( python_id int(10) NOT NULL AUTO_INCREMENT COMMENT '注释:id', python_name varchar(100) DEFAULT NULL COMMENT '注释:姓名', python_phone varchar(100) DEFAULT NULL COMMENT '注释:手机', python_sex varchar(100) DEFAULT NULL COMMENT '注释:性别', python_age varchar(100) DEFAULT NULL COMMENT '注释:年龄', python_height varchar(100) DEFAULT NULL COMMENT '注释:身高', python_weight varchar(100) DEFAULT NULL COMMENT '注释:体重', python_native_place varchar(100) DEFAULT NULL COMMENT '注释:体重', PRIMARY KEY (python_id) COMMENT '注释:主键约束python_id' )COMMENT '注释:python测试' """ """添加列(默认添加最后一列)""" sql_04 = """ ALTER TABLE test1_python ADD COLUMN python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯' """ """添加列(添加到指定位置)""" sql_05 = """ ALTER TABLE test1_python ADD COLUMN python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯' AFTER python_sex """ """添加列(添加到第一列)""" sql_06 = """ ALTER TABLE test1_python ADD COLUMN python_native_place varchar(100) DEFAULT NULL COMMENT '籍贯' AFTER """ """添加/修改表注释""" sql_07 = """ ALTER TABLE test1_python COMMENT 'python测试666' """ """修改列的类型和长度、添加/修改列注释""" sql_08 = """ ALTER TABLE test1_python MODIFY python_native_place varchar(100) COMMENT '籍贯' """ """删除指定列""" sql_09 = """ ALTER TABLE test1_python DROP COLUMN python_native_place """ """指定插入数据01""" sql_10 = """ INSERT INTO test1_python SET python_name = '猪油仔', python_phone = '19911111111', python_sex = '男', python_age = '22岁', python_height = '160CM', python_weight = '50KG' """ """删除指定数据""" sql_11 = """ DELETE A FROM test1_python A WHERE A.python_id = 5 """ """删除表的所有数据""" sql_12 = """ DELETE A FROM test1_python A """ """查询表数据:ORDER BY 字段名+DESC=降序=321————ORDER BY 字段名+ASC=升序=123—————ORDER BY RAND( )=随机排序""" sql_13 = """ SELECT * FROM test1_python WHERE python_name='猪油仔' AND python_phone='19911111111' ORDER BY RAND() LIMIT 10000 """ """连表查询表数据:ORDER BY 字段名+DESC=降序=321————ORDER BY 字段名+ASC=升序=123—————ORDER BY RAND( )=随机排序""" sql_14 = """ SELECT A.python_id, A.python_name, B.python_id, B.python_name FROM test1_python A INNER JOIN test2_python B ON A.python_id = B.python_id WHERE A.python_name='猪油仔' AND A.python_phone='19911111111' ORDER BY RAND() LIMIT 10000 """ sql = sql_14 print("打印执行的mysql语句:", sql) print("---------------------------------------------------------------------------------------------------------------") """使用execute方法执行SQL语句""" cursor.execute(sql) """commit()提交事务,插入数据时必须要提交事务,否则执行语句后,数据库是不会有新增数据的""" db.commit() """fetchone():获取一条数据、fetchall():获取所有数据""" data = cursor.fetchall() """打印查询结果""" print("打印执行mysql语句的结果:", data) """关闭游标""" cursor.close() """关闭连接""" db.close()