Pandas dataframe to SQL table using presto-python-client syntax error: mismatched input ';'

Question

I am connecting to a presto db, trying to write a dataframe into a sql table. I can "CREATE TABLE" but df.to_sql throws a syntax error:

PrestoUserError: PrestoUserError(type=USER_ERROR, name=SYNTAX_ERROR, message="line 1:61: mismatched input ';'. Expecting: '%', '*', '+', '-', '.', '/', 'AND', 'AT', 'EXCEPT', 'FETCH', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OFFSET', 'OR', 'ORDER', 'UNION', 'WINDOW', '[', '||', <EOF>", query_id=20220420_155736_92426_nkmur)

Code:

conn = prestodb.dbapi.connect(
    host = hostname,
    port = 8443,
    user = username,
    catalog = 'the-catalog',
    http_scheme = 'https',
    auth = prestodb.auth.BasicAuthentication(username, password),
)

cur = conn.cursor()
query = '''create table if not exists employees (id integer, name varchar(10), salary integer, dept_id integer)'''

cur.execute(query)
temp = cur.fetchall()

data = {'id':[1,2],'name':['fname','lname'],'salary':[1000,10001],'dept_id':[3,2]}
df = pd.DataFrame(data, columns= ['id','name','salary','dept_id'])

#Everything works perfect above. The error is in this line-
df.to_sql('employees', conn, if_exists='replace', index = False)

Answer 1

SQLAlchemy have limited dialect options or databases that it can connect to.

Refer : https://docs.sqlalchemy.org/en/13/dialects/ for the list.

Presto is not part of it.