Re: Extracting DB schema (newbie Q)
Thanks, James, but John Gordon identified my usage error so I'm good to go now. On Mon, 14 May 2012 09:28:06 -0700 (PDT), james hedley wrote: >On Monday, 14 May 2012 17:01:49 UTC+1, Steve Sawyer wrote: >> Brand-new to Python (that's a warning, folks) >> >> Trying to write a routine to import a CSV file into a SQL Server >> table. To ensure that I convert the data from the CSV appropriately, >> I"m executing a query that gives me the schema (data column names, >> data types and sizes) from the target table. >> >> What I think I want to do is to construct a dictionary using the >> column names as the index value, and a list containing the various >> attributes (data type, lenghth, precision). >> >> If this is NOT a good approach (or if there is a better approach), >> please issue a dope-slap, ignore the rest of this post and set me >> straight. >> >> If this is a good approach, I ran into a problem populating the >> dictionary as I couldn't seem to figure out how to make the update() >> method work by passing the name property of the row object; I kept >> getting a "keyword can't be an expression" error. >> >> What I was able to make work was to construct the command as a string >> and run exec( >> ), but seems there shoudl be a more >> direct way of updating the dictionary. >> >> TIA. > >Could you provide some demo code? Something minimal but runnable, which >results in the error you're getting would be best. --Steve-- -- http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
On Monday, 14 May 2012 17:01:49 UTC+1, Steve Sawyer wrote: > Brand-new to Python (that's a warning, folks) > > Trying to write a routine to import a CSV file into a SQL Server > table. To ensure that I convert the data from the CSV appropriately, > I"m executing a query that gives me the schema (data column names, > data types and sizes) from the target table. > > What I think I want to do is to construct a dictionary using the > column names as the index value, and a list containing the various > attributes (data type, lenghth, precision). > > If this is NOT a good approach (or if there is a better approach), > please issue a dope-slap, ignore the rest of this post and set me > straight. > > If this is a good approach, I ran into a problem populating the > dictionary as I couldn't seem to figure out how to make the update() > method work by passing the name property of the row object; I kept > getting a "keyword can't be an expression" error. > > What I was able to make work was to construct the command as a string > and run exec( > ), but seems there shoudl be a more > direct way of updating the dictionary. > > TIA. Could you provide some demo code? Something minimal but runnable, which results in the error you're getting would be best. -- http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
On Tue, May 15, 2012 at 5:09 AM, Steve Sawyer wrote:
> Thanks - now, given my query that returns the table structure, this
> works fine:
>
> table_dict = {}
> table_specs = cursor.execute(query_string)
> for row in table_specs:
> row_dict = {}
> row_dict['type'] = row.DataType
> row_dict['size'] = row.Length
> table_dict[row.name] = row_dict
>
> table_dict['path']['type'] #-> 'nvarchar'
> table_dict['path']['size'] # -> 200
> table_dict['Artist']['size'] #-> 50
>
> Is this (nesting dictionaries) a good way to store multiple attributes
> associated with a single key value?
There's lots of options. The dictionary works; or you could create a
class for your record - also, check out namedtuple from the
collections module, which is a shortcut to the second option.
# Option 2
class Field:
def __init__(self,datatype,length):
self.datatype=datatype
self.length=length
# Option 3:
import collections
Field=collections.namedtuple('Field',('datatype','length'))
table_dict = {}
table_specs = cursor.execute(query_string)
for row in table_specs:
# Option 1: no need to start with an empty dictionary and then populate it
table_dict[row.name] = {'type': row.DataType, 'size': row.Length}
# Option 2 or 3: your own record type, or a namedtuple
table_dict[row.name] = Field(row.DataType,row.Length)
# Option 1:
table_dict['path']['type'] #-> 'nvarchar'
table_dict['path']['size'] # -> 200
table_dict['Artist']['size'] #-> 50
# Option 2 or 3
table_dict['path'].datatype #-> 'nvarchar'
table_dict['path'].size # -> 200
table_dict['Artist'].size #-> 50
You'll notice that I've used 'datatype' rather than 'type' - the
latter would work, but since 'type' has other meaning (it's the class
that all classes subclass, if that makes sense), I like to avoid using
it.
The choice between these three options comes down to style, so pick
whichever one "feels best" to you.
ChrisA
--
http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
Thanks, John.
>What are you trying that isn't working?
Typical newbie trick - trying to make things more complicated than
they are. I didn't realize that syntax would establish the key/value
pairs of the dictionary - I thought that would only allow you to
establish the value to correspond to a specified (and pre-exiting)
key, not establish the key as well.
I was establishing the dictionary then trying to use dict.update() to
append key/value pairs.
Thanks - now, given my query that returns the table structure, this
works fine:
table_dict = {}
table_specs = cursor.execute(query_string)
for row in table_specs:
row_dict = {}
row_dict['type'] = row.DataType
row_dict['size'] = row.Length
table_dict[row.name] = row_dict
table_dict['path']['type'] #-> 'nvarchar'
table_dict['path']['size'] # -> 200
table_dict['Artist']['size'] #-> 50
Is this (nesting dictionaries) a good way to store multiple attributes
associated with a single key value?
On Mon, 14 May 2012 17:05:17 + (UTC), John Gordon
wrote:
>In Steve Sawyer
> writes:
>
>> What I think I want to do is to construct a dictionary using the
>> column names as the index value, and a list containing the various
>> attributes (data type, lenghth, precision).
>
>If you're using just the column name as the dictionary key, make sure
>there are no duplicate column names among all your tables.
>
>> If this is a good approach, I ran into a problem populating the
>> dictionary as I couldn't seem to figure out how to make the update()
>> method work by passing the name property of the row object; I kept
>> getting a "keyword can't be an expression" error.
>
>The general syntax for assigning to a dictionary is:
>
> my_dictionary[key] = value
>
>What are you trying that isn't working?
--Steve--
--
http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
In Steve Sawyer writes: > What I think I want to do is to construct a dictionary using the > column names as the index value, and a list containing the various > attributes (data type, lenghth, precision). If you're using just the column name as the dictionary key, make sure there are no duplicate column names among all your tables. > If this is a good approach, I ran into a problem populating the > dictionary as I couldn't seem to figure out how to make the update() > method work by passing the name property of the row object; I kept > getting a "keyword can't be an expression" error. The general syntax for assigning to a dictionary is: my_dictionary[key] = value What are you trying that isn't working? -- John Gordon A is for Amy, who fell down the stairs gor...@panix.com B is for Basil, assaulted by bears -- Edward Gorey, "The Gashlycrumb Tinies" -- http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
Steve Sawyer wrote:
Brand-new to Python (that's a warning, folks)
Trying to write a routine to import a CSV file into a SQL Server
table. To ensure that I convert the data from the CSV appropriately,
I"m executing a query that gives me the schema (data column names,
data types and sizes) from the target table.
What I think I want to do is to construct a dictionary using the
column names as the index value, and a list containing the various
attributes (data type, lenghth, precision).
If this is NOT a good approach (or if there is a better approach),
please issue a dope-slap, ignore the rest of this post and set me
straight.
If this is a good approach, I ran into a problem populating the
dictionary as I couldn't seem to figure out how to make the update()
method work by passing the name property of the row object; I kept
getting a "keyword can't be an expression" error.
What I was able to make work was to construct the command as a string
and run exec(), but seems there shoudl be a more
direct way of updating the dictionary.
TIA.
Please post the exact traceback and the code associated with it.
Using csv.DictReader should allow you to do this in 3 lines, something like:
reader = csv.DictReader('acsvfile.csv')
myDict.update(reader)
uploadDictToDb(myDict)
--
http://mail.python.org/mailman/listinfo/python-list
Re: Extracting DB schema (newbie Q)
On Tue, May 15, 2012 at 2:01 AM, Steve Sawyer wrote: > Brand-new to Python (that's a warning, folks) It's one we're familiar with :) Welcome! > Trying to write a routine to import a CSV file into a SQL Server > table. To ensure that I convert the data from the CSV appropriately, > I"m executing a query that gives me the schema (data column names, > data types and sizes) from the target table. > > What I think I want to do is to construct a dictionary using the > column names as the index value, and a list containing the various > attributes (data type, lenghth, precision). That seems reasonable; I might consider a namedtuple or perhaps another dictionary, but what you have is usable. > If this is a good approach, I ran into a problem populating the > dictionary as I couldn't seem to figure out how to make the update() > method work by passing the name property of the row object; I kept > getting a "keyword can't be an expression" error. Not sure what you're attempting to do; you'd do well to post your code (preferably a minimal test-case) and the exact traceback. But my guess is that you're creating a list and then trying to use the update() method. If that's so, you can simplify it a lot: columninfo[columnname] = [type, length, precision] > What I was able to make work was to construct the command as a string > and run exec(), but seems there shoudl be a more > direct way of updating the dictionary. Agreed, you shouldn't normally need to exec to achieve what you want! But post your failing code and we'll be better able to help. Chris Angelico -- http://mail.python.org/mailman/listinfo/python-list
Extracting DB schema (newbie Q)
Brand-new to Python (that's a warning, folks) Trying to write a routine to import a CSV file into a SQL Server table. To ensure that I convert the data from the CSV appropriately, I"m executing a query that gives me the schema (data column names, data types and sizes) from the target table. What I think I want to do is to construct a dictionary using the column names as the index value, and a list containing the various attributes (data type, lenghth, precision). If this is NOT a good approach (or if there is a better approach), please issue a dope-slap, ignore the rest of this post and set me straight. If this is a good approach, I ran into a problem populating the dictionary as I couldn't seem to figure out how to make the update() method work by passing the name property of the row object; I kept getting a "keyword can't be an expression" error. What I was able to make work was to construct the command as a string and run exec(), but seems there shoudl be a more direct way of updating the dictionary. TIA. -- http://mail.python.org/mailman/listinfo/python-list
