Python使用sqlite和Mysql数据库笔记

Python使用sqlite如下:
创建sqlite语句:

CREATE TABLE history (
id??? INTEGER???????? PRIMARY KEY ASC AUTOINCREMENT,
url?? VARCHAR( 100 ),
title TEXT,
date? DATETIME??????? DEFAULT ( ( datetime( ‘now’, ‘localtime’ )? )? )
);

import sqlite3
#建立数据库连接
conn = sqlite3.connect(“db.dll”)
#创建游标
cur = conn.cursor()

#增加
exec_insert = “INSERT INTO history (url,title) VALUES (‘%s’,’%s’)”
cur.execute(exec_insert % (“url2″,”title2”))
conn.commit()

#修改
test_url = “http://127.0.0.2/”
test_title = “dedecms by python spiders!”
exec_update = “UPDATE history SET url=’%s’, title=’%s’ WHERE url=’%s’ AND title=’%s’ ”
cur.execute(exec_update % (test_url,test_title,”url2″,”title2″))
conn.commit()
#MD,开始因为创建了索引、测试半天没能修改、、、

#删除
cur.execute(“DELETE FROM history WHERE id = 1”)
conn.commit()

#查询
exec_select = cur.execute(“select * from history”)
print cur.fetchall()

 

Python使用mysql如下:

import MySQLdb
#建立数据库连接
conn = MySQLdb.connect(
host = ‘localhost’,
user = ‘root’,
passwd = ‘root’,
db = ‘android’,
port = 3306,
charset =’utf8′,
)
cur = conn.cursor()

#增删改查直接用 %s 替换sql语句就行
“”” –MySQLdb
CREATE DATABASE p2p DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `android`.`p2p_caiji` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`url` text,
`keywords` text,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
“””
sqli_select = “SELECT * FROM p2p_caiji WHERE url = %s” #查询是否有存在的记录 sql模版
sqli_insert = “INSERT INTO p2p_caiji (id,url,keywords,date) VALUES(NULL,%s,%s,CURRENT_TIMESTAMP)” #添加记录 sql模版

#查询
exec_select = cur.execute(sqli_select,url)

#添加
cur.execute(sqli_insert,(res[‘url’],res[‘key’]))
conn.commit() #提交事物

修改和删除一样,使用sql模版 %s 替换即可。

发表评论

电子邮件地址不会被公开。 必填项已用*标注