3
$\begingroup$

What is the best way to read SQL database in to Tensorflow?

Currently, I am using Postgres on server and developed DL algorithm on Tensorflow using Jupyter Lab. How can I import data into Jupyter Lab using tf.data API? I do not want to store the data in the disk and keep running the algorithm when the new data arrives.

It seems like tf.data.experimental.SqlDataset only support for sqlite.

(NOTE: I did not upgrade my Tensorflow, so, I am using tf.contrib.data.SqlDataset() for the minimal working example.)

I migrated the data from PostgreSQL to SQLite3 and using

#Ignore the warnings
import warnings
warnings.filterwarnings("ignore")

import tensorflow as tf
#To start an input pipeline, you must define a source
dataset = tf.contrib.data.SqlDataset("sqlite", "/home/musara1/musara_dev.sqlite3",
                                 "SELECT * FROM basetable LIMIT 10",
                                 (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32))

iterator = dataset.make_one_shot_iterator()
next_element = iterator.get_next()
# Prints the rows of the result set of the above query.
sess=tf.InteractiveSession()

print(sess.run(next_element))

I can print the next element. However, there are other transformations I need to do on the dataset. such as splitting into training/validation/testing and getting rid of some columns et cetera. However, the output of tf.contrib.data.SqlDataset() is for me <SqlDataset shapes: ((), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), ()), types: (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32)>

I have 25 columns and tf.contrib.data.SqlDataset() creates 25 different tensorflow.python.framework.ops.Tensor. How can I bring them together? Therefore, I can use tf.data.Dataset.from_tensor_slices()?

$\endgroup$
  • $\begingroup$ So just to confirm, you've already migrated entire dataset from PostgreSQL to SQLite3, right? If not, you may like to go through this, as you've already noticed SQLite is a better companion with TF. $\endgroup$ – Random Nerd Nov 24 '18 at 6:22
  • $\begingroup$ Yes I did. My problem is how to manipule SqlDataset on Tensorflow using tf.data API $\endgroup$ – ARAT Nov 24 '18 at 14:35
1
$\begingroup$

You use the methods on SqlDataset to manipulate the data. For example, create a train/test split with:

test_dataset = dataset.take(1000) 
train_dataset = dataset.skip(1000)

I would get rid of unneeded columns in the SELECT statement to reduce the size of the data as early as possible.

| improve this answer | |
$\endgroup$
0
$\begingroup$

That import step doesn't look right. Can you try it like this?

import pypyodbc 
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=ServerName;"
                        "Database=TestDB;"
                        "Trusted_Connection=yes;")

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Actions')

for row in cursor:
    print('row = %r' % (row,))
| improve this answer | |
$\endgroup$
  • $\begingroup$ yes I could use external library which I did in the end using psycopg2 but I wanted to use Tensorflow's its own function and manipulate there using tf.data API. $\endgroup$ – ARAT Feb 22 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.