heatmap/query_db.py at master · zebfross/heatmap · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#!/usr/bin/python

import sys
import MySQLdb

db_connection = {}
db_cursor = {}

def openDbConnection():
	global db_connection
	global db_cursor
	try:
		db_connection = MySQLdb.connect(host = "localhost", user = "zebfross_root", passwd="zebfross", db="zebfross_realestate")
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])
		sys.exit(1)

	db_cursor = db_connection.cursor()

def closeDbConnection():
	global db_connection
	global db_cursor
	db_cursor.close()
	db_connection.commit()
	db_connection.close()

def executeUpdate(query):
	global db_cursor
	try:
		db_cursor.execute(query)
		rowsAffected = db_cursor.rowcount
		return rowsAffected
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])
		print "SQL Update Error: ", query
		return 0

def executeQuery(query):
	global db_cursor
	try:
		db_cursor.execute(query)
		rows = db_cursor.fetchall()
		return rows
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])
		print "SQL Query Error: ", query
		return {}

create_table_query = """
		create table property
		(
			id varchar(15) primary key not null,
			address varchar(100),
			longitude decimal(12, 9),
			latitude decimal(12, 9),
			price decimal(12, 2),
			posted_at timestamp,
			crawled_at timestamp
		)
	"""