python3数据库配置,远程连接mys

数据运维 2023-08-12 宇宙之一粟手机阅读

连接mysql数据库

查询mysql版本信息

<code> #!/usr/bin/python3 1. -*- coding: utf-8 -*- """ 1. Author: EricRay 1. Created Time : 2017-12-27 16:45:02 1. File Name: db.py 1. Description:查询Mysql版本信息 """ import pymysql #打开数据库连接 db = pymysql.connect("192.168.122.58","eric","eric2017","test") #pymysql.connect("localhost/IP","username","pwd","databs" ) #使用cursor()方法创建一个游标对象 cursor cursor = db.cursor() #使用execute()方法执行sql查询 cursor.execute("select VERSION()") #使用 fetchone()方法获取单条数据 data = cursor.fetchone() print ("database version : %s" % data) #关闭连接 db.close()

建表

<code> #!/usr/bin/python3 1. -*- coding: utf-8 -*- """ 1. Author: EricRay 1. Created Time : 2017-12-27 18:31:13 1. File Name: cr_t.py 1. Description: """ import pymysql #打开数据库连接 db = pymysql.connect("192.168.122.58","eric","lyd2017","test") cursor = db.cursor() cursor.execute("drop table if exists employee") #预处理语句建表 sql = """create table employee( tname varchar(20) not null default '', age int, sex varchar(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8""" cursor.execute(sql) db.close()

插入数据

<code> #!/usr/bin/python3 1. -*- coding: utf-8 -*- """ 1. Author: EricRay 1. Created Time : 2017-12-27 18:38:56 1. File Name: insert.py 1. Description: """ #!/usr/bin/python3 import pymysql 1. 打开数据库连接 db = pymysql.connect("192.168.122.58","eric","lyd2017","test" ) 1. 使用cursor()方法获取操作游标 cursor = db.cursor() sql = "insert into employee(tname,age,sex) \ values('%s','%d','%s')" % \ ('Mary',18,'F') try: cursor.execute(sql) db.commit() except: db.rollback() db.close()

查询

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。