title: Proper way to store GUID in sqlite
body: <p>I'm looking to store GUIDs in my SQLite database, using C# and python clients.</p>
  
  <p>Creating a database, and inserting a row, storing the GUID as a string:</p>
  
  <pre><code>conn = sqlite3.connect(filename)
  
  c = conn.cursor()
  
  # Create the table. Yes, I know GUID isn't a real SQLite datatype.
  c.execute('CREATE TABLE test (guid GUID PRIMARY KEY, name text)')
  
  u = uuid.uuid4()
  print u
  t = (str(u), 'foo')
  c.execute('INSERT INTO test VALUES (?,?)', t)
  
  conn.commit()
  conn.close()
  </code></pre>
  
  <p>Fetching:</p>
  
  <pre><code># ...
  c.execute('SELECT * FROM test WHERE guid = "c1332103-6031-4ff7-b610-f8f3b940fa66"')
  print c.fetchone()
  </code></pre>
  
  <p>This all works perfectly. Using the default Python <code>__str__</code> representation of the UUID works well.</p>
  
  <pre class="lang-none prettyprint-override"><code>C:\Users\Jonathon&gt;makedb.py test.db
  c1332103-6031-4ff7-b610-f8f3b940fa66
  
  C:\Users\Jonathon&gt;opendb.py test.db
  (u'c1332103-6031-4ff7-b610-f8f3b940fa66', u'foo')
  </code></pre>
  
  <hr>
  
  <p>My doubts arise from using <a href="http://www.sqliteexpert.com/index.html" rel="noreferrer">SQLite Expert</a>. It appears that SQLite Expert is happy with my GUID datatype declaration:</p>
  
  <p><img src="https://i.stack.imgur.com/U9X0B.png" alt="enter image description here"></p>
  
  <p>But, if I edit a row:</p>
  
  <p><img src="https://i.stack.imgur.com/qyHuO.png" alt="enter image description here"></p>
  
  <p>it seems that it changes the datatype! My <code>SELECT</code> from before yields <code>None</code>, and if I <code>SELECT *</code>, I see that it is no longer a simple unicode string:</p>
  
  <pre class="lang-none prettyprint-override"><code>C:\Users\Jonathon&gt;opendb.py test.db
  (&lt;read-write buffer ptr 0x02239520, size 16 at 0x02239500&gt;, u'foo')
  </code></pre>
  
  <hr>
  
  <p>Looking at the data on disk, you can see that the GUID is stored in binary, after SQLite Expert touches it:</p>
  
  <p>Before - the GUID is ASCII text:</p>
  
  <p><img src="https://i.stack.imgur.com/kwj5F.png" alt="enter image description here"></p>
  
  <p>After - the previous data is garbage, and a binary version of the GUID exists:</p>
  
  <p><img src="https://i.stack.imgur.com/D42fq.png" alt="enter image description here"></p>
  
  <hr>
  
  <p>So what is the <em>"correct"</em> way to store GUIDs in SQLite, specifically using Python? Later, I will have C# code interacting with this as well, and want to make sure I'm going things the "right" way.</p>