慕课 python 操作数据库

test_connection

import MySQLdb

conn = MySQLdb.Connect(
    host = '127.0.0.1',
    port = 3306,
    user = '*****',
    passwd = '*****',
    db = 'czy',
    charset = 'utf8'
    )

cursor = conn.cursor()

print conn
print cursor

cursor.close()
conn.close()

结果：

<_mysql.connection open to '127.0.0.1' at 21f0980>
<MySQLdb.cursors.Cursor object at 0x0228BA30>

test_coursor：

import MySQLdb

conn = MySQLdb.Connect(
    host = '127.0.0.1',
    port = 3306,
    user = '****',
    passwd = '****',
    db = 'czy',
    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()
print rs

cursor.close()
conn.close()

结果：

9
(1L, u'name1')
((2L, u'name2'), (3L, u'name3'), (4L, u'name4'))
((5L, u'name5'), (6L, u'name6'), (7L, u'name7'), (8L, u'name8'), (9L, u'name9'))

test_select:

import MySQLdb

conn = MySQLdb.Connect(
    host = '127.0.0.1',
    port = 3306,
    user = '**',
    passwd = '**',
    db = 'czy',
    charset = 'utf8'
    )

cursor = conn.cursor()

sql = "select * from user"
cursor.execute(sql)

rs = cursor.fetchall()

for row in rs:
    print "userid = %s,username = %s" % row

cursor.close()
conn.close()

结果：

userid = 1,username = name1
userid = 2,username = name2
userid = 3,username = name3
userid = 4,username = name4
userid = 5,username = name5
userid = 6,username = name6
userid = 7,username = name7
userid = 8,username = name8
userid = 9,username = name9

test_iud:

import MySQLdb

conn = MySQLdb.Connect(
    host = '127.0.0.1',
    port = 3306,
    user = '',
    passwd = '',
    db = 'czy',
    charset = 'utf8'
    )

cursor = conn.cursor()

sql_insert = "insert into user(userid,username) values(10,'name10')"
sql_update = "update user set username='name91' where userid=9"
sql_delete = "delete from user where userd<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, e:
    print e
    conn.rollback()



cursor.close()
conn.close()

结果（没有影响数据库，因为第三条语句失败了）：

1
1
(1054, "Unknown column 'userd' in 'where clause'")