闲暇之余学习了下python。并通过python连接数据库,以及简单的对数据库的操作。
先贴一段已经实现的代码,我使用的版本为3.6,所以安装的是pysql来实现连接的。执行pip install pymysql
即可安装。
# conding=utf-8
import pymysql
# 连接数据库 并添加cursor游标
conn = pymysql.connect('localhost','root','0000','myapp')
cursor = conn.cursor()
# 创建数据表的sql 语句 并设置name_id 为主键自增长不为空
sql_createTb = """CREATE TABLE MONEY (
name_id INT NOT NULL AUTO_INCREMENT,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
PRIMARY KEY(name_id))
"""
# 插入一条数据到moneytb 里面。
sql_insert = "insert into money(LAST_NAME,AGE,SEX) values('de2',18,'0')"
# 在 execute里面执行SQL语句
cursor.execute(sql_createTb)
cursor.execute(sql_insert)
print(cursor.rowcount)
conn.commit()
conn.close()
cursor.close()
再上一段代码比较详细的对数据表增删改查的操作,以及出现异常回滚事务:
import pymysql
# 连接数据库
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
passwd='888999',
db='myapp',
charset='utf8'
)
# 获取游标
cursor = conn.cursor()
# 使用 execute() 方法执行 SQL 的增删改查 insert delete update select
sql_select = "select * from user"
sql_insert = "insert into user(id,name,age) values(8,'pythonN8','18')"
sql_update = "update user set name='helloPy' where id=7"
sql_delete = "delete from user where d=7"
# 执行 insert 增加的语句 如果出现异常对异常处理
try:
cursor.execute(sql_insert)
print(cursor.rowcount)
cursor.execute(sql_update)
print(cursor.rowcount)
cursor.execute(sql_delete)
print(cursor.rowcount)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
# res = cursor.fetchmany(2)
# print(res)
# res = cursor.fetchall()
# print(res)
cursor.close()
conn.close()