Mercurial > hg > Lemuriformes

annotate lemuriformes/mysql.py @ 15:0d1b8bb1d97b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQL + data related functionality
author Jeff Hammel <k0scist@gmail.com>
date Sun, 10 Dec 2017 17:16:52 -0800
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
15
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
1 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
2 MySQL database connection class + helpers
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
3 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
4
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
5 import pymysql # https://github.com/PyMySQL/PyMySQL
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
6 import pymysql.cursors
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
7 import sys
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
8 from .cli import ConfigurationParser
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
9 from .sql import SQLConnection
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
10
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
11
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
12 class MySQLConnection(SQLConnection):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
13 """connection to a MySQL database"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
14
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
15 placeholder = '%s' # VALUE placeholder
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
16 connect_data_keys = ['host', 'user', 'password', 'db', 'port', 'charset']
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
17
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
18 def __init__(self, host, user, password, db, port=3306, charset='utf8mb4'):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
19 self.connect_data = {}
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
20 for key in self.connect_data_keys:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
21 self.connect_data[key] = locals()[key]
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
22
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
23
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
24 def connect(self):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
25 return pymysql.connect(**self.connect_data)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
26
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
27 def __call__(self, sql, *args):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
28
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
29 with self.connect() as cursor:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
30 try:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
31 cursor.execute(sql, args)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
32 except TypeError:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
33 print ((sql, args))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
34 raise
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
35 try:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
36 result = cursor.fetchall()
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
37 except:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
38 result = None
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
39 self.connect().commit()
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
40 return result
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
41
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
42 def tables(self):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
43 """return tables"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
44
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
45 data = self("show tables")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
46 return [item[0] for item in data]
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
47
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
48 def drop(self, table):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
49
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
50 if table not in self.tables():
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
51 return
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
52 self("drop table if exists {table}".format(table=table))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
53
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
54 def create(self, table, *columns):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
55 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
56 columns -- each column should be a 2-tuple
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
57 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
58
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
59 sql = "CREATE TABLE {table} ({columns}) DEFAULT CHARSET=utf8mb4"
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
60
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
61 # format columns
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
62 _columns = ', '.join(["{0} {1}".format(column, _type)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
63 for column, _type in columns])
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
64
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
65 # execute query
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
66 self(sql.format(table=table, columns=_columns))
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
67
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
68 def insert(self, table, **row):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
69 """insert a `row` into `table`"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
70
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
71 assert row
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
72 keys = row.keys()
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
73 values = [row[key] for key in keys]
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
74 self(self.insert_sql(table=table,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
75 columns=keys),
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
76 *values)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
77
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
78 def insert_many(self, table, columns, values):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
79 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
80 insert many rows into `table`
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
81 columns -- list of columns to insert
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
82 """
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
83
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
84 # https://stackoverflow.com/questions/13020908/sql-multiple-inserts-with-python
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
85 # It may be more efficient to flatten the string
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
86 # instead of using `.executemany`; see
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
87 # https://stackoverflow.com/questions/14011160/how-to-use-python-mysqldb-to-insert-many-rows-at-once
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
88
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
89 with self.connect() as connection:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
90 sql = self.insert_sql(table=table, columns=columns)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
91 connection.executemany(sql, values)
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
92 self.connect().commit()
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
93
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
94
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
95 class MySQLParser(ConfigurationParser):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
96 """command line parser for MySQL"""
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
97 # TODO: obsolete!
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
98
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
99 def add_arguments(self):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
100 self.add_argument('host', help="SQL host")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
101 self.add_argument('db', help="database to use")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
102 self.add_argument('-u', '--user', dest='user', default='root',
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
103 help="MySQL user [DEFAULT: %(default)s]")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
104 self.add_argument('-p', '--password', dest='password',
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
105 help="MySQL password [DEFAULT: %(default)s]")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
106
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
107 def connection(self):
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
108 if self.options is None:
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
109 raise Exception("parse_args not called successfully!")
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
110
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
111 return MySQLConnection(host=self.options.host,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
112 user=self.options.user,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
113 password=self.options.password,
0d1b8bb1d97b SQL + data related functionality
Jeff Hammel <k0scist@gmail.com>
parents:
diff changeset
114 db=self.options.db)