SQLite3 & Py: Error Inserting hyphenated string
<p>I am doing a simple script in which Python reads a bunch of tab-separated files, and line by line enters the first item in the line onto an sqlite3 table. The process works well, except for the actual data. The data that is being sent to me is in the format 123-4567890-1234567 (3-7-7). Instead of seeing the full string in the database, I get the arithmetical result of the three numbers in the string, i.e. -5802334. </p>

<p>I've tried all kinds of combinations with quotes, such as <code>Lines[0] = "'" + Lines[0] + "'"</code> (I get an error that the last item is an unrecognized token) or <code>Lines[0] = Lines[0].replace('-','_')</code> ("OperationalError: unrecognized token: "114_6555410_7421863")<br>
. </p>

<p>Can you tell me what I'm doing incorrectly, and/or how to overcome this problem?</p>

<p>Here is my full code:</p>

<pre><code>import sqlite3, os, fnmatch, csv, datetime

Homedir = os.path.expanduser('~')
DBFile =  Homedir + '\\Desktop\\AmazonProg\\AmazonOrders.sqlite'
Rawpathin = '\\\\idc-v-lapedi01\\amtu2\\Data\\production\\reports\\'
#TableName = 'OrderNums'
#IdColumn = 'Orderid'
#POColumn = 'PONum'
sTimestamp = datetime.datetime.now().strftime('%Y%m%d%H%M')
Lines = []

conn = sqlite3.connect(DBFile)
c = conn.cursor()

Amzfiles=fnmatch.filter(os.listdir(Rawpathin), 'order*.txt')
for Files in Amzfiles:
    with open(Rawpathin + Files, "r") as Source:
        Reader = csv.reader(Source, delimiter = '\t')
        for Lines in Reader:
            if Lines[0] == 'order-id':
                pass
            elif len(Lines[0])== 19:
                c.execute("INSERT OR IGNORE INTO OrderNums (Orderid, PONum, Timestamp) VALUES ({idf}, {v1}, {v2})".format(idf=Lines[0], v1=Lines[0][12:], v2 = sTimestamp))
            else:
                pass
conn.commit()
conn.close()
</code></pre>

<p>Thank you in advance. </p>

<p>Bottom line is that you should not be using <code>str.format</code> for this as it is not secure and it is also, in your case, not producing the result you are expecting.</p>

<p>Fortunately, this problem was solved long ago. For you, just change your <code>c.execute</code> line to this:</p>

<pre><code>c.execute("INSERT OR IGNORE INTO OrderNums (Orderid, PONum, Timestamp)
          VALUES (?,?,?)",(Lines[0],Lines[0][12:],sTimestamp))
</code></pre>

<p>Probably a good idea to review the docs - there are some very helpful examples included:</p>

<p><a href="https://docs.python.org/3.5/library/sqlite3.html" rel="nofollow">https://docs.python.org/3.5/library/sqlite3.html</a></p>

python sqlite3 insert-into