Databases
StackOverflow2013
Postsdbo
POInsert Python List (JSON or otherwise) into MySQL databse
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInsert Python List (JSON or otherwise) into MySQL databse
    primarykey
    data
    text
    <p>So I have a bunch of array data in Python. Well, rather, I have a list of lists. I'm trying to store this array into a single cell in a MySQL database. I've attempted to use JSON to serialize my data, but perhaps I do not understand how JSON works.</p> <p>So after connecting to my database: (I've tried LONGTEXT and LONGBLOB datatypes for Upstream and Downstream</p> <pre><code>cur = con.cursor() cur.execute("CREATE TABLE IF NOT EXISTS 963168MBV17A(Id INT AUTO_INCREMENT PRIMARY KEY, Rev INT, Part VARCHAR(15), SN INT(7), Date DATE, Time TIME, Iterations INT(3), Upstream LONGBLOB, Downstream LONGBLOB, ResultList LONGTEXT, Result CHAR(1), Report LONGBLOB)") </code></pre> <p>I take my list of lists called upstream_data and downstream_data and do:</p> <pre><code>export_upstream = json.dumps(upstream_data) export_downstream = json.dumps(downstream_data) </code></pre> <p>Then I execute the SQL commands:</p> <pre><code>cur = con.cursor() sql_input = "INSERT INTO 963168MBV17A(Rev, Part, SN, Iterations, Date, Time, Upstream, Downstream, ResultList, Result, Report) VALUES('503', '100-120970-0031', '1594539', '%s', '%s', '%s', '%s', '%s', 0, P, 0" %(export_date, export_time, export_numtests, export_upstream, export_downstream) cur.execute(sql_input) </code></pre> <p>referencing an answer by Mordi (http://stackoverflow.com/questions/4251124/inserting-json-into-mysql-using-python), I even tried:</p> <pre><code>export_upstream = json.dumps(json.dumps(upstream_data)) export_downstream = json.dumps(json.dumps(downstream_data)) </code></pre> <p>But regardless I end up with the error:</p> <pre><code>Traceback (most recent call last): File "P:\Projects\testing database\scrap\test.py", line 83, in &lt;module&gt; cur.execute(sql_input) File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute self.errorhandler(self, exc, value) File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1") </code></pre> <p>Moreover, when I do a</p> <pre><code>print "about to execute(%s)" % sql_input </code></pre> <p>I see that the JSON object is appearing as a long string with lots of single quotes all over the place (for the list, and on the outside to represent a string). When I did the json.dumps(json.dumps(upstream_data)), the inside quotations become double quotes "" and preceded by \ characters. Still, though, I get the same error.</p> <p>Any ideas? If not, any better way to store Python array/list data into a single MySQL cell?</p> <p>output <a href="http://dl.dropbox.com/u/2319996/error.txt" rel="nofollow">here</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsirgogo
    1. USsirgogo
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POInsert Python List (JSON or otherwise) into MySQL databse
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POInsert Python List (JSON or otherwise) into MySQL databse
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload