Python & Pandas : (pymysql.err.OperationalError) (2003, “Can't connect to MySQL server on 'localhost'...
$begingroup$
I'm writing a Python Script to store JSON data into MySQL Database. I used pandas
to store into MySQL Database.
I used two different modules (MySQLdb
and sqlalchemy
) to connect to MySQL dtaabase.
Python Code:
user@sys:~$ cat pandas_to_mysql.py
import pandas
from pandas.io import sql
from sqlalchemy import create_engine
import MySQLdb
json_string_final='[{"id": 772, "name": "abcd"}]'
df=pandas.DataFrame(eval(json_string_final))
print(df)
#----Using MySQLdb---
con=MySQLdb.connect(host='localhost',user='user1',passwd='pwd',db='myDB')
try:
df.to_sql(con=con, name='myTable1', if_exists='replace')
except Exception as error1:
print("Error with connection 1 ------> ")
print(error1)
#----Using Engine----
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}".format(user="user1",pw="pwd",db="myDB"))
try:
df.to_sql(con=engine, name='myTable2', if_exists='replace')
except Exception as error1:
print("Error with connection 2 ----->")
print(error1)
I used two diffrent connections to store the data into MySQL dtabase. But connection was not established. Below is the error message.
Output:
user@sys:~$ python3 pandas_to_mysql.py
id name
0 772 abcd
Error with connection 1 ------>
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting
Error with connection 2 ----->
(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' (timed out)")
(Background on this error at: http://sqlalche.me/e/e3q8)
But i'm able to connect to MySQL database manually from the same terminal using the following command.
MySQL Connection:
user@sys:~$ mysql -u user1 -h localhost -D myDB -p
It means, there is no issues with host, user, password, database name. Then what is the issue?
python pandas sql
New contributor
$endgroup$
add a comment |
$begingroup$
I'm writing a Python Script to store JSON data into MySQL Database. I used pandas
to store into MySQL Database.
I used two different modules (MySQLdb
and sqlalchemy
) to connect to MySQL dtaabase.
Python Code:
user@sys:~$ cat pandas_to_mysql.py
import pandas
from pandas.io import sql
from sqlalchemy import create_engine
import MySQLdb
json_string_final='[{"id": 772, "name": "abcd"}]'
df=pandas.DataFrame(eval(json_string_final))
print(df)
#----Using MySQLdb---
con=MySQLdb.connect(host='localhost',user='user1',passwd='pwd',db='myDB')
try:
df.to_sql(con=con, name='myTable1', if_exists='replace')
except Exception as error1:
print("Error with connection 1 ------> ")
print(error1)
#----Using Engine----
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}".format(user="user1",pw="pwd",db="myDB"))
try:
df.to_sql(con=engine, name='myTable2', if_exists='replace')
except Exception as error1:
print("Error with connection 2 ----->")
print(error1)
I used two diffrent connections to store the data into MySQL dtabase. But connection was not established. Below is the error message.
Output:
user@sys:~$ python3 pandas_to_mysql.py
id name
0 772 abcd
Error with connection 1 ------>
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting
Error with connection 2 ----->
(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' (timed out)")
(Background on this error at: http://sqlalche.me/e/e3q8)
But i'm able to connect to MySQL database manually from the same terminal using the following command.
MySQL Connection:
user@sys:~$ mysql -u user1 -h localhost -D myDB -p
It means, there is no issues with host, user, password, database name. Then what is the issue?
python pandas sql
New contributor
$endgroup$
add a comment |
$begingroup$
I'm writing a Python Script to store JSON data into MySQL Database. I used pandas
to store into MySQL Database.
I used two different modules (MySQLdb
and sqlalchemy
) to connect to MySQL dtaabase.
Python Code:
user@sys:~$ cat pandas_to_mysql.py
import pandas
from pandas.io import sql
from sqlalchemy import create_engine
import MySQLdb
json_string_final='[{"id": 772, "name": "abcd"}]'
df=pandas.DataFrame(eval(json_string_final))
print(df)
#----Using MySQLdb---
con=MySQLdb.connect(host='localhost',user='user1',passwd='pwd',db='myDB')
try:
df.to_sql(con=con, name='myTable1', if_exists='replace')
except Exception as error1:
print("Error with connection 1 ------> ")
print(error1)
#----Using Engine----
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}".format(user="user1",pw="pwd",db="myDB"))
try:
df.to_sql(con=engine, name='myTable2', if_exists='replace')
except Exception as error1:
print("Error with connection 2 ----->")
print(error1)
I used two diffrent connections to store the data into MySQL dtabase. But connection was not established. Below is the error message.
Output:
user@sys:~$ python3 pandas_to_mysql.py
id name
0 772 abcd
Error with connection 1 ------>
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting
Error with connection 2 ----->
(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' (timed out)")
(Background on this error at: http://sqlalche.me/e/e3q8)
But i'm able to connect to MySQL database manually from the same terminal using the following command.
MySQL Connection:
user@sys:~$ mysql -u user1 -h localhost -D myDB -p
It means, there is no issues with host, user, password, database name. Then what is the issue?
python pandas sql
New contributor
$endgroup$
I'm writing a Python Script to store JSON data into MySQL Database. I used pandas
to store into MySQL Database.
I used two different modules (MySQLdb
and sqlalchemy
) to connect to MySQL dtaabase.
Python Code:
user@sys:~$ cat pandas_to_mysql.py
import pandas
from pandas.io import sql
from sqlalchemy import create_engine
import MySQLdb
json_string_final='[{"id": 772, "name": "abcd"}]'
df=pandas.DataFrame(eval(json_string_final))
print(df)
#----Using MySQLdb---
con=MySQLdb.connect(host='localhost',user='user1',passwd='pwd',db='myDB')
try:
df.to_sql(con=con, name='myTable1', if_exists='replace')
except Exception as error1:
print("Error with connection 1 ------> ")
print(error1)
#----Using Engine----
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}".format(user="user1",pw="pwd",db="myDB"))
try:
df.to_sql(con=engine, name='myTable2', if_exists='replace')
except Exception as error1:
print("Error with connection 2 ----->")
print(error1)
I used two diffrent connections to store the data into MySQL dtabase. But connection was not established. Below is the error message.
Output:
user@sys:~$ python3 pandas_to_mysql.py
id name
0 772 abcd
Error with connection 1 ------>
Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during bytes formatting
Error with connection 2 ----->
(pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' (timed out)")
(Background on this error at: http://sqlalche.me/e/e3q8)
But i'm able to connect to MySQL database manually from the same terminal using the following command.
MySQL Connection:
user@sys:~$ mysql -u user1 -h localhost -D myDB -p
It means, there is no issues with host, user, password, database name. Then what is the issue?
python pandas sql
python pandas sql
New contributor
New contributor
New contributor
asked 2 days ago
Dipankar NaluiDipankar Nalui
1203
1203
New contributor
New contributor
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
The following works fine for me!
df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
$endgroup$
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["$", "$"], ["\\(","\\)"]]);
});
});
}, "mathjax-editing");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "557"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Dipankar Nalui is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f46874%2fpython-pandas-pymysql-err-operationalerror-2003-cant-connect-to-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$begingroup$
The following works fine for me!
df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
$endgroup$
add a comment |
$begingroup$
The following works fine for me!
df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
$endgroup$
add a comment |
$begingroup$
The following works fine for me!
df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
$endgroup$
The following works fine for me!
df.to_sql(con="mysql://user:password@localhost/database_name, name='myTable2', if_exists='replace')
answered yesterday
Siddhi Kiran BajracharyaSiddhi Kiran Bajracharya
3026
3026
add a comment |
add a comment |
Dipankar Nalui is a new contributor. Be nice, and check out our Code of Conduct.
Dipankar Nalui is a new contributor. Be nice, and check out our Code of Conduct.
Dipankar Nalui is a new contributor. Be nice, and check out our Code of Conduct.
Dipankar Nalui is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Data Science Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdatascience.stackexchange.com%2fquestions%2f46874%2fpython-pandas-pymysql-err-operationalerror-2003-cant-connect-to-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown