MySQLdb and dictcursor

[borris]

doesn anyone know a good reference, tute or examples of
MySQLdb's dictCursor
I want to pass dictionaries into the sql exec statements.
I could only succeed with text as values


this is how I got the cursor object to call cursor.execute("query")
connection = MySQLdb.connect( host = "localhost", user =
self.config.userName, passwd = self.config.password, cursorclass =
MySQLdb.cursors.DictCursor)

thanks

 

[Christoph Haas]

doesn anyone know a good reference, tute or examples of
MySQLdb's dictCursor
I want to pass dictionaries into the sql exec statements.
I could only succeed with text as values

A german linux magazin has an article about passing a *list* of items at
http://www.linux-magazin.de/Artikel/ausgabe/2002/06/python-api/python-api.html

For those who don't understand german here comes an example on how to pass
lists:

cursor.execute("""INSERT INTO Adressen (Name, Strasse, PLZ, Ort)
VALUES (%s, %s, %s, %s)""",
[ ('Dr. Hans Mustermann', 'Musterstraße 13', 50823, 'Köln'),
('Peter Lustig', 'Im Bauwagen 2', 50827, 'Porz'),
('Edmund Stoiber', 'Spendensumpf 1', 47011, 'Bimbesdorf'),
('Onkel Hotte', 'Im Siff 42', 57072, 'Siegen'),
('Gerhard Schröder', 'Großmaulweg 2', 11901, 'Worthülsen') ]
)

However the DictCursor is helpful for *returning* dictionaries of values
instead of just tuples. I always use DictCursor because it makes it
clearer which rows I want to access in the result. But DictCursor can't
pass dictionaries in a cursor.execute AFAIKT.

But with dict.iteritems that shouldn't be hard to send dictionary items to
the SQL database in a loop either.

Regards
Christoph

 

[Dennis Lee Bieber]

doesn anyone know a good reference, tute or examples of
MySQLdb's dictCursor
I want to pass dictionaries into the sql exec statements.
I could only succeed with text as values

dictCursor
RETURNS the results as a dictionary; it doesn't affect how
parameters are passed in.

Normally results are a (list or tuple) where you have to know the
order of the fields specified in the query:

cr.execute("select a, c, b from table")
dt = cr.fetchone()

dt is a (list/tuple) with (a_value, c_value, b_value)

With a dictCursor you get

dcr.execute("select a, c, b from table")
ddt = dcr.fetchone()

ddt is a dictionary of {"a" : a_value, "b" : b_value, "c" : c_value}


MySQLdb nominally uses just the %s placeholder style, but I think it
will also function with %(name)s format...

cr.execute(
"insert into table (c, a, b) values (%(c)s, %(a)s, %(b)s",
ddt)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[blank]

dictCursor
RETURNS the results as a dictionary; it doesn't affect how
parameters are passed in.

thats how I was using it

Normally results are a (list or tuple) where you have to know the
order of the fields specified in the query:

cr.execute("select a, c, b from table")
dt = cr.fetchone()

dt is a (list/tuple) with (a_value, c_value, b_value)

With a dictCursor you get

dcr.execute("select a, c, b from table")
ddt = dcr.fetchone()

ddt is a dictionary of {"a" : a_value, "b" : b_value, "c" : c_value}


MySQLdb nominally uses just the %s placeholder style, but I think it
will also function with %(name)s format...

cr.execute(
"insert into table (c, a, b) values (%(c)s, %(a)s, %(b)s",
ddt)

sounds a lot simpler, ill give it a go later. thanks

 

[inhahe]

here's how I do it.

def fix(a):
return a.replace("\\","\\\\").replace("'","\\'")
def insertdict(table, **dict):
cursor.execute("insert into %s (%s) values (%s)" % (table,
fix(",".join([str(x[0]) for x in dict.items() if not x[1] is None])),
",".join(["'%s'" % fix(str(x)) for x in dict.values() if not x is None])))

how do I use dictCursor, though? i tried cursor = db.dictCursor() and it said 'Connection' object has no attribute 'dictCursor'. i know there's a way to get mysqldb to return results in a dictionary, but i can't find it..