什么是
Python相当于Perl的DBI以及如何使用它?更具体地说,Python等效于以下Perl代码是什么?
- use DBI;
- # connect to a MysqL database
- my $dbh = DBI->connect("dbi:MysqL:database=$database; host=localhost; port=3306",$user,$pass);
- # select and read a few rows
- my $sth = $dbh->prepare("SELECT id,name FROM table WHERE id <= ?;");
- $sth->execute(321);
- while (my @row = $sth->fetchrow_array) {
- my $id = $row[0];
- my $name = $row[1];
- print "$id. $name\n";
- }
- # write to the database
- $sth = $dbh->prepare("INSERT INTO table (id,name) VALUES (?,?);");
- $sth->execute(123,"foo");
解决方法
- import MysqLdb.cursors
- db = MysqLdb.connect(db=database,host=localhost,port=3306,user=user,passwd=pass,cursorclass=MysqLdb.cursors.DictCursor)
- cur = db.cursor()
- #this is not string interpolation,everything is quoted for you automatically
- cur.execute("select id,name from table where id = %s",(321,))
- for row in cur.fetchall():
- print "%s. %s" % (row['id'],row['name'])
- cur.execute("insert into table (id,name) values (%s,%s)",(123,'foo'))
- db.commit() # required,because autocommit is off by default
Python数据库API使用常见的convention,在不同的数据库中几乎相同(但不完全!).您可以阅读MysqLdb文档here.
还有一个功能更丰富的MysqL接口,称为oursql.它具有真正的参数化(不仅仅是美化字符串插值),服务器端游标,数据流等.