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]