def get_ppgdate(date):
"""
'date' may be a datetime.date type or string with format 'dd-mm-yyyy' or
'dd/mm/yyyy'. Function returns psycopg2.Date
"""
if type(date) == datetime.date:
return psycopg2.Date(date.year, date.month, date.day)
if type(date) != str:
raise StandardError(
"Invalid date type. It must be 'datetime.date' or " +
"string with format 'dd-mm-yyyy' or 'dd/mm/yyyy'")
match = re.match('(^(\d{1,2})[\/|-](\d{1,2})[\/|-](\d{4})$)', date)
if match:
dd = int(match.groups()[1])
mm = int(match.groups()[2])
yyyy = int(match.groups()[3])
if not ((1 < dd < 31) or (1 < mm < 12)):
raise StandardError(
"Invalid month or day value. Format: 'dd-mm-yyyy' or 'dd/mm/yyyy'"
)
return psycopg2.Date(yyyy, mm, dd)
else:
raise StandardError(
"Invalid date format. It must be 'dd-mm-yyyy' or 'dd/mm/yyyy'")
def insert_book(self, book):
"""
Insert book in database
:returns: True if succeeds False if not
"""
q = "INSERT INTO books (title, author, cover, progress, file_name, pages, description, identifier, publisher, date, rights, tags) values (%s, %s, %s, 0, %s, 0, %s, %s, %s, %s, %s, %s);"
try:
try:
cover_image = book[2].data
except:
cover_image = book[2]
if not book[2]: # If cover image is missing unset entry
cover_image = None
self.cursor.execute(
q,
(
book[0], # title
book[1], # author
cover_image,
book[3], # file
book[4], # descr
book[5], # ident
book[6], # publisher
datetime.datetime.now(),
book[8], # rights
book[9], # tags
),
)
self.config.logger.info(book[0][0:80])
return True
except Exception as e:
if e.pgcode == '22007': # psycopg2's error code for invalid date
book[7] = psycopg2.Date(int(book[7]), 1, 1)
self.insert_book(book)
raise e
def test_date_time_allocation_bug(self):
d1 = psycopg2.Date(2002,12,25)
d2 = psycopg2.DateFromTicks(time.mktime((2002,12,25,0,0,0,0,0,0)))
t1 = psycopg2.Time(13,45,30)
t2 = psycopg2.TimeFromTicks(time.mktime((2001,1,1,13,45,30,0,0,0)))
t1 = psycopg2.Timestamp(2002,12,25,13,45,30)
t2 = psycopg2.TimestampFromTicks(
time.mktime((2002,12,25,13,45,30,0,0,0)))
def makeDate():
month = random.randint(1, 12)
if month == 2:
date = random.randint(1, 28)
elif month in [4, 6, 9, 11]:
date = random.randint(1, 30)
else:
date = random.randint(1, 31)
year = 2020
Date = psycopg2.Date(year, month, date)
return Date
def convert_DATE(dt):
val = dt.value
if havemx and isinstance(val, mx.DateTime.DateTimeType):
return psycopg.DateFromMx(val)
elif isinstance(val, datetime.date):
return psycopg.Date(dt.year, dt.month, dt.day)
elif isinstance(val, (int, float, long)):
d = datetime.date.fromtimestamp(val)
return psycopg.Date(d.year, d.month, d.day)
elif isinstance(val, (tuple, list)):
return psycopg.Date(*val[:3])
elif isinstance(val, basestring):
for f in date_formats:
try:
t = time.strptime(val, f)[:3]
except ValueError:
continue
else:
return psycopg.Date(*t)
else:
raise ValueError, "cannot parse date format: '%s'" % val
raise ValueError, val
def createsuperuser(self):
self.db.cursor.execute("SELECT * FROM interface_user")
_user_list = self.db.cursor.fetchall()
if len(_user_list) > 0:
return False
else:
today = datetime.date.today()
date = psycopg2.Date(today.year, today.month, today.day)
self.db.cursor.execute(
'INSERT INTO interface_user (username, password, is_staff, is_active, is_superuser, '
'date_joined, first_name, last_name, ulvl, email ) '
'VALUES( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
("pyshelf", make_password("pyshelf"), True, True, True, date,
"pyshelf", "default", 1, "*****@*****.**"))
self.db.commit()
self.db.close()
return True
try:
t = time.strptime(val, f)
except ValueError:
continue
else:
return psycopg.TimestampFromTicks(time.mktime(t))
else:
raise ValueError, "cannot parse timestamp format: '%s'" % val
raise ValueError, val
_converters = {
datetime.datetime:
lambda x: psycopg.TimestampFromTicks(time.mktime(x.timetuple())),
datetime.date:
lambda x: psycopg.Date(x.year, x.month, x.day),
DATE:
convert_DATE,
TIMESTAMP:
convert_TIMESTAMP,
BINARY:
lambda x: psycopg.Binary(x.value),
INTERVAL:
lambda x: x.value
}
if havemx:
# add automatic wrapping for mx.DateTime types
_converters[mx.DateTime.DateTimeType] = TimestampFromMx
_converters[
mx.DateTime.DateTimeDeltaType] = lambda x: x.strftime("%d:%H:%M:%S")
def save_weibo_db(weibo, screen_name, keyword):
"""
将用户微博信息保存到数据库中
:param weibo:微博字典数据
:param screen_name:微博用户名
:param keyword:微博关键词
:return:
"""
database = "weiboplatform"
username = "******"
pwd = "admin"
localhost = "127.0.0.1"
port = "5432"
conn = psycopg2.connect(database=database,
user=username,
password=pwd,
host=localhost,
port=port)
cur = conn.cursor()
try:
weibo_uid = weibo["user_uid"]
weibo_screen_name = screen_name
weibo_keyword = keyword
weibo_scheme = weibo["scheme"]
weibo_created_at = weibo["created_at"]
text = weibo["text"]
text = text.replace("\'", "'")
text = text.replace('\"', '"')
weibo_text = text
bs_obj = BeautifulSoup(weibo_text, "html5lib")
weibo_raw_text = bs_obj.get_text()
weibo_pics = weibo["pics"]
weibo_comments_count = weibo["comments_count"]
weibo_attitudes_count = weibo["attitudes_count"]
weibo_reposts_count = weibo["reposts_count"]
# 去掉图片的http
for url in weibo_pics:
if "http:" in url:
url = url[5:]
elif "https:" in url:
url = url[6:]
# 处理图片链接长度问题
if len(weibo_pics) < 9:
for i in range(9 - len(weibo_pics)):
weibo_pics.append(None)
i0, i1, i2, i3, i4, i5, i6, i7, i8 = weibo_pics[0], weibo_pics[1], weibo_pics[2], weibo_pics[3], \
weibo_pics[4], weibo_pics[5], weibo_pics[6], weibo_pics[7], weibo_pics[8]
insert_sql = "insert into weibodata(uid,weibo_screen_name,weibo_scheme," \
"weibo_created_at,keyword,weibo_text,weibo_raw_text," \
"weibo_comments_count,weibo_attitudes_count,weibo_reposts_count," \
"weibo_pics1,weibo_pics2,weibo_pics3,weibo_pics4," \
"weibo_pics5,weibo_pics6,weibo_pics7,weibo_pics8,weibo_pics9) " \
"values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
cur.execute(insert_sql, (
weibo_uid,
weibo_screen_name,
weibo_scheme,
psycopg2.Date(weibo_created_at[0], weibo_created_at[1],
weibo_created_at[2]),
weibo_keyword,
weibo_text,
weibo_raw_text,
weibo_comments_count,
weibo_attitudes_count,
weibo_reposts_count,
i0,
i1,
i2,
i3,
i4,
i5,
i6,
i7,
i8,
))
conn.commit()
return True
except (BaseException, FileExistsError, FileNotFoundError,
RequestException, ConnectionError, ConnectTimeout,
ContentDecodingError) as e:
conn.rollback()
print(e.args)
finally:
conn.close()
def GetDateAndTimeForPostgresql(self, dateAndTime):
timeNum = self.GetDateAndTime(dateAndTime)
date = psycopg2.Date(timeNum[0], timeNum[1], timeNum[2])
time = psycopg2.Time(timeNum[3], 0, 0)
return [date, time]
