Python操作MySQL数据库

https://www.cnblogs.com/hanfanfan/p/10398244.html

 

# -*- coding:UTF-8 -*-
# date 20200428
import MySQLdb

# 连接数据库

conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
# db = MySQLdb.connect(host="10.238.160.1",user="root",passwd = "root@2017",db = "test",port =3306 ,charset='utf8')
# print conn  #<_mysql.connection open to '10.238.160.1' at 13c6270>

# 使用cursor() 方法创建一个游标对象
cursor = conn.cursor()
# print cursor # <MySQLdb.cursors.Cursor object at 0x7fcd5b7e6810>

# 使用execute()方法执行SQL查询
cursor.execute("select version()")

# 使用fetchone() 方法获取单条数据
data = cursor.fetchone()

print "Database version : %s" %data

# 关闭数据库连接
conn.close()

 

# -*- coding:UTF-8 -*-



import  MySQLdb

# 执行SQL
# conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
#
# curson = conn.cursor()
#
# curson.execute('DROP TABLE IF EXISTS EMPLOYEE')
#
#
# sql = '''CREATE TABLE `employee` (
#   `first_name` varchar(255) DEFAULT NULL COMMENT '姓',
#   `last_name` varchar(255) DEFAULT NULL COMMENT '名',
#   `age` int(11) DEFAULT NULL COMMENT '年龄',
#   `sex` varchar(255) DEFAULT NULL COMMENT '性别',
#   `income` varchar(255) DEFAULT NULL COMMENT '收入'
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# '''
#
# curson.execute(sql)
#
# conn.close()


# 插入数据

# conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
#
# curson = conn.cursor()
#
# sql = """INSERT INTO employee (
# 	first_name,
# 	last_name,
# 	age,
# 	sex,
# 	income
# )
# VALUES
# 	(
# 		'peng',
# 		'jia',
# 		18,
# 		'女',
# 		1000000
# 	)
# """
# try:
#
#     curson.execute(sql)
#     conn.commit()
# except:
#     conn.rollback()
#
# conn.close()

# 查询数据
# conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
#
# cursor = conn.cursor()
#
# sql = """select *  from employee where income  >= 1000"""
#
# try:
#     cursor.execute(sql)
#     results = cursor.fetchall()
#     print results
#
#     for row in results:
#         fname = row[0]
#         lname = row[1]
#         age = row[2]
#         sex = row[3]
#         income = row[4]
#         print "fname = %s" %fname
#         print row
#
#
# except:
#     print ("unable to fetch data!")


# 更新数据

# conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
#
# cursor = conn.cursor()
#
# # sql = """update employee set age = age + 1 where  last_name = '海航'"""
#
# sql = """select sleep(60)"""
#
# try:
#     cursor.execute(sql)
#     conn.commit()
#
# except:
#     conn.rollback()
#
#
# conn.close()


# 删除操作

# conn = MySQLdb.connect("10.238.160.1","root","root@2017","test",charset = 'utf8')
#
# curson = conn.cursor()
#
# sql = """DELETE from employee WHERE last_name = '海航'"""
#
# try:
#     curson.execute(sql)
#     conn.commit()
#
# except:
#     conn.rollback()
#
# 
# conn.close()