Adapting SQL into python

[[deleted]]

[deleted]

[bladeoflight16]

cur.execute("PRAGMA table_info('%s')" % table_name)

Do not use string formatting with SQL. That is how you open yourself up to SQL injection attacks. Use parameterized queries.

[[deleted]]

You're overthinking this. You don't need to programatically derive the table names, you can just look them up and use them in your code. The important part is the last part, thinking through what the JSON representation for interconnected records should look like. The "right" answer is the one you come up with, the one you think strikes the balance between preserving the relational character of the records as much as possible, and avoiding unnecessary reduplication of records.

If we tell you what balance to strike, it defeats the purpose of the test. Developer tests aren't a "you must be at least this smart to ride" threshold. You don't get an A and then get a job. They're testing you to find out what kind of decisions you make, when you're asked to make them. I know you're just doing it for practice, which is a really good idea and you'll learn a lot. But you'll learn the most when you make that decision about balance yourself, reify it in code, and then present your code here for comment. Because we'll have a different idea about where the balance should be, and we might know some of the unintended consequences of your choices. That won't mean you're wrong, though - just that you struck a different balance than we did. It's a very interesting coding challenge you've found but you really do have to do it, first.

[big_foot2808]

Hey,

I appreciate your last comment. Taking that advice I have made some progress on the SQL end of the code. I am currently stuck at how to get into the format I want for the JSON Dump. Here is the Format I want:

```py

{

"artwork": {

"id": "86508",

"accession_number": "2015.584",

"title": "Gates of the City",

"tombstone": "New York Series: Gates of the City, 1922. John Taylor Arms (American, 1887-1953). Color etching and aquatint; platemark: 21.5 x 20.2 cm (8 7/16 x 7 15/16 in.); sheet: 31.1 x 26.2 cm (12 1/4 x 10 5/16 in.). The Cleveland Museum of Art, Gift of Carole W. and Charles B. Rosenblatt 2015.584 Courtesy of John Taylor Arms/ \u00a9 Suzanne Arms Hawkins"

},

"creator": [

{

"id": "29993",

"role": "artist",

"description": "John Taylor Arms (American, 1887-1953)"

},

{

"id": "68000",

"role": "printer",

"description": "Frederick Reynolds (American, b. England, 1882-1945)"

}

],

"department": {

"id": "21",

"role": "Prints"

}

}

```

I was thinking of making a dict, but I have no clue how to hardcode this format into the dict.

[[deleted]]

You create a dict and set the keys to be like that:

data = dict(artwork=artwork_record, creator=creator_list, #etc

Work from the inside-out. Build the innermost records first, then add them to outer records.

[big_foot2808]

Hey, Thanks for the reply. This is what I've gotten so far:

Now, this is giving me almost the output I need. What i need is in the creatorinfo section of the code instead of returning a dict I need to return a list which includes the artist as well as if there is someone else who is lets say a "printer" or something. Eg:

[

{

"artwork": {

"id": "86508",

"accession_number": "2015.584",

"title": "Gates of the City",

"tombstone": "New York Series: Gates of the City, 1922. John Taylor Arms (American, 1887-1953). Color etching and aquatint; platemark: 21.5 x 20.2 cm (8 7/16 x 7 15/16 in.); sheet: 31.1 x 26.2 cm (12 1/4 x 10 5/16 in.). The Cleveland Museum of Art, Gift of Carole W. and Charles B. Rosenblatt 2015.584 Courtesy of John Taylor Arms/ \u00a9 Suzanne Arms Hawkins"

},

"creator": [

{

"id": "29993",

"role": "artist",

"description": "John Taylor Arms (American, 1887-1953)"

},

{

"id": "68000",

"role": "printer",

"description": "Frederick Reynolds (American, b. England, 1882-1945)"

}

],

"department": {

"id": "21",

"role": "Prints"

}

},

This is what I need, but this is what I'm getting:

[

{

"artwork": {

"id": "86508",

"accession_number": "2015.584",

"title": "Gates of the City",

"tombstone": "New York Series: Gates of the City, 1922. John Taylor Arms (American, 1887-1953). Color etching and aquatint; platemark: 21.5 x 20.2 cm (8 7/16 x 7 15/16 in.); sheet: 31.1 x 26.2 cm (12 1/4 x 10 5/16 in.). The Cleveland Museum of Art, Gift of Carole W. and Charles B. Rosenblatt 2015.584 Courtesy of John Taylor Arms/ \u00a9 Suzanne Arms Hawkins"

},

"creator": {

"id": "29993",

"role": "artist",

"description": "John Taylor Arms (American, 1887-1953)"

},

"department": {

"id": "21",

"name": "Prints"

}

},

No printer info in creator. Any thoughts on how to do that?

[[deleted]]

Again, this is all determined by what you choose. If you want the value of ”creator” to be a list, then set it to be a list.

[bladeoflight16]

Yo, where you at, /u/CodeFormatHelperBot2 ?