-- 1-2 Python DB API介绍 https://www.imooc.com/video/9221
python db api : https://www.python.org/dev/peps/pep-0249/
-- 1-3 Python开发MySQL环境 https://www.imooc.com/video/9222
https://sourceforge.net/projects/mysql-python/# : python访问mysql接口程序下载
-- 安装python-MySQLdb似乎没报错。但是在python中加载是报下面的错 https://zhidao.baidu.com/question/497339004.html
-- 2-1 Python-数据库连接对象 https://www.imooc.com/video/9214
-- D:\project_py\py_001\imooc\test_connection.py
import MySQLdb
conn= MySQLdb.Connect(
host='127.0.0.1',
port =3306,
user='root',
password='1',
db='immoc',
charset='utf8'
)
cursor=conn.cursor()
print conn
print cursor
cursor.close()
conn.close()
-- 2-2 Python-游标对象cursor https://www.imooc.com/video/9215
CREATE TABLE `user` (
`usrid` int(11) NOT NULL ,
`usrname` varchar(100) default NULL,
PRIMARY KEY (`usrid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- D:\project_py\py_001\imooc\test_cursor_methods.py
import MySQLdb
conn= MySQLdb.Connect(
host='127.0.0.1',
port =3306,
user='root',
password='1',
db='immoc',
charset='utf8'
)
cursor=conn.cursor()
sql='select * from user'
cursor.execute(sql)
print cursor.rowcount
rs=cursor.fetchone()
print rs
rs=cursor.fetchmany(3)
print rs
rs=cursor.fetchall()
for row in rs:
print 'userid=%s,username=%s'%row
cursor.close()
conn.close()
-- 3-2 Python-实例演示inset-upd https://www.imooc.com/video/9217
-- D:\project_py\py_001\imooc\test_iud.py
import MySQLdb
conn= MySQLdb.Connect(
host='127.0.0.1',
port =3306,
user='root',
password='1',
db='immoc',
charset='utf8'
)
cursor=conn.cursor()
sql_insert='insert into user(userid,username) values(10,"name10")'
sql_update='update user set username="nameXX" where userid=9'
sql_delete='delete from user where userid<3'
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()
cursor.close()
conn.close()
-- 4-1 [Python操作MySQL]银行转 https://www.imooc.com/video/9218
-- 4-2 [Python操作MySQL]准备数 https://www.imooc.com/video/9219
CREATE TABLE `account` (
`acctid` int(11) NOT NULL COMMENT '账户id',
`money` int(11) default NULL COMMENT '余额',
PRIMARY KEY (`acctid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 在pycharm中，如何快速建立类、方法 https://segmentfault.com/q/1010000004696562
在标黄的地方alt+回车试试，或者shift+insert
-- D:\project_py\py_001\imooc\transer_money.py
#! coding=utf-8
import sys
import MySQLdb
class TransferMoney( object ):
def __init__(self, conn):
self.conn = conn
def transfer(self, source_acctid, target_acctid, money):
try:
self.check_acct_available( source_acctid )
self.check_acct_available( target_acctid )
self.has_enough_money( source_acctid, money )
self.reduce_money( source_acctid, money )
self.add_money( target_acctid, money )
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e
def check_acct_available(self, acctid):
cursor = self.conn.cursor()
try:
sql = 'select * from account where acctid=%s' % acctid
cursor.execute( sql )
print 'check_acct_available' + sql
rs = cursor.fetchall()
if len( rs ) != 1:
raise Exception( '帐号%s不存在' % acctid )
finally:
cursor.close()
def has_enough_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = 'select * from account where acctid=%s and money>%s' % (acctid, money)
cursor.execute( sql )
print 'check_acct_available' + sql
rs = cursor.fetchall()
if len( rs ) != 1:
raise Exception( '帐号%s没有足够的钱' % acctid )
finally:
cursor.close()
def reduce_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = 'update account set money=money-%s where acctid=%s' % (money, acctid)
cursor.execute( sql )
print 'reduce_money' + sql
if cursor.rowcount != 1:
raise Exception( '帐号%s减款失败' % acctid )
finally:
cursor.close()
def add_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = 'update account set money=money+%s where acctid=%s' % (money, acctid)
cursor.execute( sql )
print 'reduce_money' + sql
if cursor.rowcount != 1:
raise Exception( '帐号%s加款失败' % acctid )
finally:
cursor.close()
if __name__ == '__main__':
source_acctid = sys.argc[1]
target_acctid = sys.argv[2]
money = sys.argv[3]
conn = MySQLdb.Connect( host='127.0.0.1', user='root', password='1', prot=3306, db='immoc' )
tr_money = TransferMoney( conn )
try:
tr_money.transfer( source_acctid, target_acctid, money )
except Exception as e:
print '出现问题:' + str( e )
finally:
conn.close()
-- https://www.imooc.com/video/9220
|
|
转播
