# -*- coding: utf-8 -*-
# sudo pip install mysql-connector-python # pure python alternative to mysql-python which depends on MySQL c libraries
import mysql.connector
import argparse
class ExampleMySQL(object):
def __init__(self, user, password, database, socket_file=None):
self.user = user
self.password = password
self.database = database
self.connection = mysql.connector.connect(user=self.user, password=self.password, database=self.database,
unix_socket=socket_file)
# TODO: allow customizing host and port
self.cursor = self.connection.cursor(buffered=True)
def quit(self):
if self.connection:
self.connection.commit() # ensure everything is committed
if self.cursor:
self.cursor.close()
if self.connection:
self.connection.close()
def get_table_column_headers(self, table_name, ascii_only=False):
result = list()
self.cursor.execute("SELECT column_name FROM information_schema.columns "
"WHERE table_schema='" + self.database + "'" + " AND table_name='" + table_name + "'")
for row in self.cursor.fetchall():
if ascii_only:
result.append(str(row[0])) # downgrade from utf8 to string
else:
result.append(row[0])
return result
def get_table_contents(self, table_name, fields):
result = list()
fields_string = ','.join(fields)
self.cursor.execute("SELECT " + fields_string + " FROM " + table_name)
row = self.cursor.fetchone()
while row:
result_row = dict()
for index in xrange(len(fields)):
column_name = fields[index]
value = row[index]
# print column_name, value
result_row[column_name] = value
result.append(result_row)
row = self.cursor.fetchone()
return result
def get_tables( self ):
self.cursor.execute( "SHOW TABLES" )
tables = list()
tuples = mycursor.fetchall()
for item in tuples:
tables.append( str( item[0] ) )
return tables
def truncate_table( self, table_name ):
query = ( "truncate table " + table_name )
self.cursor.execute( query )
self.connection.commit()
def truncate_tables( self, tables ):
for table in tables:
self.truncate_table( table )
def insert(self, table_name, column_csv, value_csv):
self.cursor.execute("INSERT into " + table_name + "(" + column_csv + ") VALUES (" + value_csv + ")")
self.connection.commit()
def reset_owner_email(self, email):
self.cursor.execute("update users set email='" + email + "' where id=1")
self.connection.commit()
def get_api_tokens( self ):
fields = self.get_table_column_headers( 'auth_tokens' )
contents = self.get_table_contents( 'auth_tokens', fields )
return contents
def insert_api_token( self, token, user_id, group_id, label, ratelimit ):
query = ( "INSERT INTO auth_tokens (token, user_id, group_id, type, label, ratelimit) VALUES (%s, %s, %s, %s, %s, %s)" )
self.cursor.execute( query, ( token, user_id, group_id, '0', label, ratelimit ) )
self.connection.commit()
if __name__ == '__main__':
parser = argparse.ArgumentParser()
parser.add_argument('-u', '--user', help='MySQL user', default='root')
parser.add_argument('-p', '--password', help='MySQL password')
parser.add_argument('-d', '--database', help='MySQL database')
parser.add_argument('-s', '--socketfile', help='MySQL socket file location')
args = parser.parse_args()
m = None
print 'connecting'
try:
m = ExampleMySQL(args.user, args.password, args.database, args.socketfile)
if m:
print 'connected'
print m.get_tables()
except Exception as error:
print 'ERROR:', error
finally:
if m and m.connection:
m.connection.close()
"""
database = 'mydatabase'
table = 'thumbnails'
insert_csv = 'user_id,path,shortcut,width,height'
cnx = None
try:
username, password = get_username_and_password()
connection = ExampleMySQL(username, password, database)
cnx = connection.connection
headers = connection.get_table_column_headers(table, ascii_only=True)
print repr(headers)
rows = connection.get_table_contents(table, headers)
print repr(rows)
connection.insert(table, insert_csv, "1,'zzz.gif','zzz',24,24")
rows = connection.get_table_contents(table, headers)
print repr(rows)
except mysql.connector.Error as error:
print 'mysql error', error.errno, error
except Exception as error:
print error
finally:
if cnx:
cnx.close()
"""
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
import mysql.connector
try:
cnx = mysql.connector.connect( user='myuser', password='mypassword', host='127.0.0.1', database='example')
mycursor = cnx.cursor( buffered=True )
mycursor.execute( "SELECT VERSION()" )
query = "SELECT * from users"
mycursor.execute( query )
print mycursor.column_names
print mycursor.rowcount
except mysql.connector.Error as error:
print 'mysql error', error.errno, error
finally:
if cnx:
cnx.close()
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# -*- coding: utf-8 -*-
import mysql.connector
def get_connection( database_name ):
cnx = mysql.connector.connect( user='root', password='mypassword', database=database_name )
return cnx
def get_table_column_headers( connection, database_name, table_name ):
result = list()
mycursor = connection.cursor( buffered=True )
mycursor.execute( "SELECT column_name FROM information_schema.columns WHERE table_schema='" + database_name + "'" + " AND table_name='" + table_name + "'" )
for row in mycursor.fetchall():
result.append( row[0] )
return result
def get_table_contents( connection, table_name, fields ):
result = list()
fields_string = ','.join( fields )
mycursor = connection.cursor( buffered=True )
mycursor.execute( "SELECT " + fields_string + " FROM " + table_name )
row = mycursor.fetchone()
while row:
result_row = dict()
for index in xrange( len( fields ) ):
column_name = fields[ index ]
value = row[ index ]
# print column_name, value
result_row[ column_name ] = value
result.append( result_row )
row = mycursor.fetchone()
return result
if __name__ == '__main__':
try:
database_name = 'mysql'
connection = get_connection( database_name )
fields = get_table_column_headers( connection, database_name, 'user' )
print repr( fields ), "\n"
contents = get_table_contents( connection, 'user', fields )
for row in contents:
print repr( row )
except mysql.connector.Error as error:
print 'mysql error', error.errno, error
finally:
if connection:
connection.close()