def process_item(self, item, spider):
if self.__class__.__name__ in spider.pipelines:
try:
conn = pymysql.connect(host='localhost', user='root', passwd='067116', db='gaode', charset='utf8')
cur = conn.cursor()
sql = "insert into %s(uid,`name`,address,tag,sub_tag,center,tel,pro_name,pro_center,city_name," \
"city_center,ad_name,ad_center,photo_url1,photo_url2,photo_url3,photo_exists)" \
"values('%s','%s','%s','%s','%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
data = (item['table_name'], item['uid'], item['name'], item['address'], item['tag'], item['sub_tag'], item['center'], item['tel'],
item['pro_name'], item['pro_center'], item['city_name'], item['city_center'], item['ad_name'],
item['ad_center'], item['photo_url1'], item['photo_url2'], item['photo_url3'], item['photo_exists'])
cur.execute(sql % data)
conn.commit()
cur.close()
conn.close()
except:
pass
else:
return item
python类connect()的实例源码
def process_item(self, item, spider):
if self.__class__.__name__ in spider.pipelines:
try:
conn = pymysql.connect(host='localhost', user='root', passwd='067116', db='gaode', charset='utf8')
cur = conn.cursor()
sql = "insert into %s(uid,`name`,address,tag,sub_tag,center,tel,pro_name,pro_center,city_name," \
"city_center,ad_name,ad_center,photo_url1,photo_url2,photo_url3,photo_exists)" \
"values('%s','%s','%s','%s','%s', '%s', '%s', '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')"
data = (item['table_name'], item['uid'], item['name'], item['address'], item['tag'], item['sub_tag'], item['center'], item['tel'],
item['pro_name'], item['pro_center'], item['city_name'], item['city_center'], item['ad_name'],
item['ad_center'], item['photo_url1'], item['photo_url2'], item['photo_url3'], item['photo_exists'])
cur.execute(sql % data)
conn.commit()
cur.close()
conn.close()
except:
pass
else:
return item
def process_item(self, item, spider):
if self.__class__.__name__ in spider.pipelines:
try:
conn = pymysql.connect(host='localhost', user='root', passwd='067116', db='gaode', charset='utf8')
cur = conn.cursor()
sql = 'insert into xishiduo(uid,`name`,address,tag,sub_tag,center,tel,pro_name,pro_center,city_name,' \
'city_center,ad_name,ad_center,distance,photo_urls,photo_exists) ' \
'values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (item['uid'], item['name'], item['address'], item['tag'], item['sub_tag'], item['center'], item['tel'],
item['pro_name'], item['pro_center'], item['city_name'], item['city_center'], item['ad_name'],
item['ad_center'], item['distance'], item['photo_urls'], item['photo_exists'])
cur.execute(sql, data)
conn.commit()
cur.close()
conn.close()
self.i += 1
print(self.i)
except:
pass
else:
return item
def process_item(self, item, spider):
if self.__class__.__name__ in spider.pipelines:
try:
conn = pymysql.connect(host='localhost', user='root', passwd='067116', db='gaode', charset='utf8')
cur = conn.cursor()
sql = 'insert into test(uid,`name`,address,tag,sub_tag,center,tel,pro_name,pro_center,city_name,' \
'city_center,ad_name,ad_center,distance,photo_urls,photo_exists,distributor) ' \
'values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
data = (item['uid'], item['name'], item['address'], item['tag'], item['sub_tag'], item['center'], item['tel'],
item['pro_name'], item['pro_center'], item['city_name'], item['city_center'], item['ad_name'],
item['ad_center'], item['distance'], item['photo_urls'], item['photo_exists'], item['distributor'])
cur.execute(sql, data)
conn.commit()
cur.close()
conn.close()
except:
print("**********exists**********")
else:
return item
def process_item(self, item, spider):
conn = pymysql.connect(host='localhost', user='root', passwd='067116', db='baike', charset='utf8')
cur = conn.cursor()
if len(item) > 1:
try:
sql = "insert into baidubaike(title,summary,basic_info,level2,pv,item_id,last_update_time,url) values(%s,%s,%s,%s,%s,%s,%s,%s)"
data = (item['title'], item['summary'], json.dumps(item['basic_info']), json.dumps(item['level2']), item['pv'], item['item_id'], item['last_update_time'], item['url'])
cur.execute(sql, data)
conn.commit()
cur.close()
conn.close()
except:
print("exists,id is %s" % item['item_id'])
else:
try:
sql = "insert into error(url) values('%s')"
cur.execute(sql % item['url'])
conn.commit()
cur.close()
conn.close()
except:
print("error page exists")
def test_example(self):
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='mysql')
cur = conn.cursor()
cur.execute("SELECT Host,User FROM user")
# print cur.description
# r = cur.fetchall()
# print r
# ...or...
u = False
for r in cur.fetchall():
u = u or conn.user in r
self.assertTrue(u)
cur.close()
conn.close()
def test_issue_17(self):
""" could not connect mysql use passwod """
conn = self.connections[0]
host = self.databases[0]["host"]
db = self.databases[0]["db"]
c = conn.cursor()
# grant access to a table to a user with a password
try:
c.execute("create table issue17 (x varchar(32) primary key)")
c.execute("insert into issue17 (x) values ('hello, world!')")
c.execute("grant all privileges on %s.issue17 to 'issue17user'@'%%' identified by '1234'" % db)
conn.commit()
conn2 = pymysql.connect(host=host, user="issue17user", passwd="1234", db=db)
c2 = conn2.cursor()
c2.execute("select x from issue17")
self.assertEqual("hello, world!", c2.fetchone()[0])
finally:
c.execute("drop table issue17")
def __init__(self,block = None,server_id = None,log_file = None,
log_pos = None,host=None,user=None,passwd=None,rollback=None,
port = None,gtid = None,_thread_id = None,stop_pos=None):
import pymysql
_remote_filed._gtid = gtid
_remote_filed._thread_id = _thread_id
self._stop_pos = stop_pos
self._log_file = log_file
self._log_pos = log_pos
self.block = block if block != None else False
self.server_id = server_id if server_id != None else 133
self.port = port if port != None else 3306
self.connection = pymysql.connect(host=host,
user=user,
password=passwd,port=self.port,
db='',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
if rollback:
_remote_filed._rollback_status = True
_rollback._myfunc = GetRollStatement(host=host,user=user,passwd=passwd,port=self.port)
self.ReadPack()
def creat_sql_file(sql_string,dataset_name):
conn = ( pymysql.connect(host = '114.34.138.146',# SQL IP
port = 3306,
user = user,# ??
password = password,# ??
database = database, # ?????
charset="utf8") ) # ??
c=conn.cursor()
c.execute( sql_string )# ???? SQL file
c.execute('ALTER TABLE `'+dataset_name+'` ADD id BIGINT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;')
c.close() # ??? SQL ???
conn.close()# ??? SQL ???
#---------------------------------------------------------------------------------
# ? ptt ??
def creat_sql_file(sql_string,dataset_name):
conn = ( pymysql.connect(host = '114.34.138.146',# SQL IP
port = 3306,
user='guest',# ??
password='123',# ??
database='guest_dataset', # ?????
charset="utf8") ) # ??
c=conn.cursor()
c.execute( sql_string )# ???? SQL file
# ? PRIMARY KEY
c.execute('ALTER TABLE `'+dataset_name+'` ADD id BIGINT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;')
c.close() # ??? SQL ???
conn.close()# ??? SQL ???
def connect(self):
await self.instance.db.connect()
await self.instance.apps.discover()
await self.instance.db.initiate()
if self.db_type != 'mysql':
raise Exception('We only support mysql converting right now!')
self.connection = pymysql.connect(
host=self.db_host, user=self.db_user, password=self.db_password, db=self.db_name, charset=self.charset,
port=self.db_port or 3306,
cursorclass=pymysql.cursors.DictCursor
)
def start(self):
if not self.connection:
raise Exception('Please connect first (connect()).')
return await self.migrate(self.connection)
def __init__(self,host='marshal.netsec.colostate.edu',user='root',passwd='****',dbname='bgp_archive'):
#Prepare DB info
self.db = pymysql.connect(host=host,
user=user,
passwd=passwd,
db=dbname)
self.dbname=dbname
def _open(self):
""" DO NOT USE THIS UNLESS YOU close() FIRST"""
try:
self.db = connect(
host=self.settings.host,
port=self.settings.port,
user=coalesce(self.settings.username, self.settings.user),
passwd=coalesce(self.settings.password, self.settings.passwd),
db=coalesce(self.settings.schema, self.settings.db),
charset=u"utf8",
use_unicode=True,
ssl=coalesce(self.settings.ssl, None),
cursorclass=cursors.SSCursor
)
except Exception as e:
if self.settings.host.find("://") == -1:
Log.error(u"Failure to connect to {{host}}:{{port}}",
host= self.settings.host,
port= self.settings.port,
cause=e
)
else:
Log.error(u"Failure to connect. PROTOCOL PREFIX IS PROBABLY BAD", e)
self.cursor = None
self.partial_rollback = False
self.transaction_level = 0
self.backlog = [] # accumulate the write commands so they are sent at once
def insert(sql):
conn = pymysql.connect("localhost", "root", "root", "test", port=3306, charset='utf8')
cur = conn.cursor()
print(sql)
sta = cur.execute(sql)
if sta == 1:
print('Done sql')
else:
print('Failed sql.')
conn.commit()
cur.close()
conn.close()
# Step2.??HTML???????MYSQL
def __init__(self):
# load(sys)
self.conn = pymysql.connect(host="127.0.0.1",
user="orange",
password="@orangeLIU3226677zc",
port=3306,
db='customs_data',
charset='utf8')
self.conn.set_charset("utf8")
def process_item(self, item, spider):
# ?spider.name?????spider
# https://segmentfault.com/q/1010000004863755
con = pymysql.connect(**DB_CONFIG)
cur = con.cursor()
sql = 'insert into section(section_url,section_name,section_article_total,top_section_num,top_section_name,updatetime) ' \
'values(%s,%s,%s,%s,%s,%s)'
values = (item['section_url'], item['section_name'], item['section_article_total'],
item['top_section_num'], item['top_section_name'], item['updatetime'])
cur.execute(sql, values) # second parameter must be iterabale
con.commit()
cur.close()
con.close()
return item
def process_item(self, item, spider):
con = pymysql.connect(**DB_CONFIG)
cur = con.cursor()
sql = 'replace into articleinfohour(section_url,article_title,' \
'article_url,article_comment,article_author,article_createtime,updatetime) ' \
'values(%s,%s,%s,%s,%s,%s,%s)'
values = (item['section_url'], item['article_title'], item['article_url'], item['article_comment'],
item['article_author'], item['article_createtime'], item['updatetime'])
cur.execute(sql, values) # second parameter must be iterabale
con.commit()
cur.close()
con.close()
return item
def __init__(self,dbname,key,citys):
self.T = datetime.datetime.strftime(datetime.datetime.now(), "%Y%m%d%H%M")
self.dbname = dbname
self.key = key
if len(citys) == 1:
self.city = citys[0]
elif len(citys) > 1:
self.city = "&".join(citys)
else:
self.city = ""
self.table_name = "{}_{}_{}".format(self.T,self.key,self.city)
self.conn = pymysql.Connect(
host="localhost",
port=3306,
user='root',
password='python',
db=self.dbname,
charset='utf8'
)
self.cursor = self.conn.cursor()
# ????????
self.create_table()
# ??????????????????????
def get_conn(sock=MyConfig.UNIX_SOCKET):
if sock != '':
return pymysql.Connect(user=MyConfig.USERNAME,passwd=MyConfig.PASSWORD,db='ec_forum',charset='utf8',unix_socket=sock)
return pymysql.Connect(host='127.0.0.1',user=MyConfig.USERNAME,passwd=MyConfig.PASSWORD,db='ec_forum',charset='utf8')
def runUserCF():
conn = pymysql.Connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'InforRec',
charset = 'utf8',
)
cursor = conn.cursor()
ratingfile = 'ratings.dat'
usercf = myUserCF.commonUserCF()
usercf.generate_dataset(ratingfile)
usercf.calculateUserSimilarity()
for u, related_users in usercf.user_sim_mat.items():
for v, count in related_users.items():
try:
sql = "insert into user_sim(user1Id, user2Id, count) values(%s, %s, %s)" % (u, v, count)
cursor.execute(sql)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
print("Message: user similiarity has been put into database.")
cursor.close()
conn.close()
def get2016AllScore():
try:
# command = "select id,pub_date,news_content from dyx_emotion_analysis where pub_date between '2016-01-01' and '2016-12-31';"
command = "select id,pub_date,news_content from wise_news where pub_date between '2016-01-01' and '2016-12-31';" # ????????
conn = pymysql.Connect(host = host,port=port,user=user,passwd=passwd,db=db,charset=charset)
cursor = conn.cursor()
except Exception as error:
print('????',end=' ')
print('????\n',error)
else:
print('????')
print('???ing')
baseStamp = time.mktime(time.strptime('2016-01-01','%Y-%m-%d')) #2016?????????
rsList = []
cursor.execute(command)
lineCount = cursor.rowcount
for i in range(lineCount):
rs = cursor.fetchone()
rsDict = dict()
rsDict['id'] = rs[0]
rsDict['date'] = rs[1]
nowStamp = time.mktime(time.strptime(rsDict['date'],'%Y-%m-%d')) # ??????
rsDict['days'] = (int)((nowStamp-baseStamp)/(24*60*60)) # ?????????
# ????????????bug
try:
news = cht_to_chs(clean_html(rs[2]))
rsDict['score'] = get_score(news)
except Exception as error:
rsDict['score'] = 0
else:
pass
print('Index at:',i,rsDict)
rsList.append(rsDict)
with open('2016AllScore.plk','wb')as f:
pickle.dump(rsList,f)
print('????')
cursor.close()
conn.close()
def runItemCF():
conn = pymysql.Connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'InforRec',
charset = 'utf8',
)
cursor = conn.cursor()
ratingfile = 'ratings.dat'
itemcf = myItemCF.commonItemCF()
itemcf.generate_dataset(ratingfile)
itemcf.calc_movie_sim()
for m1, related_movies in itemcf.movie_sim_mat.items():
for m2, count in related_movies.items():
try:
sql = "insert into item_sim(item1Id, item2Id, weight) values(%s, %s, %s)" % (m1, m2, itemcf.movie_sim_mat[m1][m2])
cursor.execute(sql)
conn.commit()
print("insert %s and %s" % (m1, m2))
except Exception as e:
print(e)
conn.rollback()
print("Message: movie similiarity has been put into database.")
for i, user in enumerate(itemcf.trainset):
rec_movies = itemcf.recommend(user)
for movie, w in rec_movies:
try:
sql = "insert into rec_result(userId, artId, weight) values(%s, %s, %s)" % (user, movie, w)
cursor.execute(sql)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
print("Message: the result of itemCF has been put into database.")
cursor.close()
conn.close()
